 |
| Excel VBA Discuss using VBA for Excel programming. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
January 31st, 2007, 02:38 PM
|
|
Registered User
|
|
Join Date: Jan 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Need a little help on college project
Hi everyone i am new here and this is my first post. Any ways the problem i am facing is executing the next code after an IF Then statment. What i am trying to do is copy data from one work sheet into another work sheet with validation.
Sub Reserve_button_Click()
If Available.Text = "No" Then MsgBox "Sorry the vehicle you selected is not available", vbExclamation
If Available.Text = "yes" Then
Title = ComboBox_Title
FirstName = Worksheets("Enquiries").Range("D21").Value
LastName = Worksheets("Enquiries").Range("D23").Value
Address1 = Worksheets("Enquiries").Range("H20").Value
Address2 = Worksheets("Enquiries").Range("H22").Value
City = Worksheets("Enquiries").Range("H24").Value
PostCode = Worksheets("Enquiries").Range("H26").Value
StartDate = Worksheets("Enquiries").Range("D12").Value
EndDate = Worksheets("Enquiries").Range("D14").Value
If FirstName = "" Then MsgBox "Please put in a First name", vbInformation
If LastName = "" Then MsgBox "Please put in a Last Name", vbInformation
If Address1 = "" Then MsgBox " Please put in vaild adree e.g. 17 walk down street", vbInformation
If City = "" Then MsgBox "Please enter a vaild city", vbInformation
If PostCode = "" Then MsgBox "Please enter a vaild postcode e.g. P7 7AB", vbInformation
If StartDate = "" Then MsgBox "Please enter the date you wish to rent the vehicle e.g. 12/02/2007", vbInformation
If EndDate = "" Then MsgBox "Please enter the date you wish to return the vehicle e.g. 12/02/2007", vbInformation
Else
After Else the code on the bottom should excute but for some reason its not working.
Row = 2
Do
Row = Row + 1
Reservation = Worksheets("Reservation").Range("A" & Row).Value
Loop Until IsEmpty(Reservation)
Worksheets("Reservation").Range("B" & Row).Value = FirstName
Worksheets("Reservation").Range("C" & Row).Value = LastName
Worksheets("Reservation").Range("D" & Row).Value = Address1
Worksheets("Reservation").Range("E" & Row).Value = Address2
Worksheets("Reservation").Range("F" & Row).Value = City
Worksheets("Reservation").Range("G" & Row).Value = PostCode
Worksheets("Reservation").Range("A" & Row).Value = Title
Worksheets("Reservation").Range("H" & Row).Value = StartDate
Worksheets("Reservation").Range("I" & Row).Value = EndDate
End If
End Sub
If any one can help that would be great
|
|
January 31st, 2007, 05:44 PM
|
|
Registered User
|
|
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
limpep,
Instead of:-
Row = 2
Do
Row = Row + 1
Reservation = Worksheets("Reservation").Range("A" & Row).Value
Loop Until IsEmpty(Reservation)
Try:-
rw = 2
Do
rw = rw + 1
Loop Until IsEmpty(Worksheets("reservation").Cells(rw, 1))
'change row to rw in any code underneath
|
|
January 31st, 2007, 06:39 PM
|
|
Registered User
|
|
Join Date: Jan 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Nop that didnt work. Any more ideas?
|
|
January 31st, 2007, 07:02 PM
|
|
Registered User
|
|
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Is your problem an error message occurs or that nothing appears in cols B to I ?
regards
|
|
January 31st, 2007, 07:11 PM
|
|
Registered User
|
|
Join Date: Jan 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
No bec before i add the validation everything was working fine when i start to add the if then and else statments alli got was the only the top code working the bottom half didnt work.
|
|
February 1st, 2007, 06:47 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
I think that you don't need the else statement where you've put it. This is because the code in this pursuant section will only fire off when Available.Text does not equal "yes" whereas the operations the code are performing seem to me to follow on from the sense checking above. I've reworked your code to what I think you meant to be running.
HTH,
Maccas
Code:
Sub Reserve_button_Click()
If Available.Text = "No" Then
MsgBox "Sorry the vehicle you selected is not available", vbExclamation
ElseIf Available.Text = "Yes" Then
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Extract the details
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Title = ComboBox_Title
FirstName = Worksheets("Enquiries").Range("D21").Value
LastName = Worksheets("Enquiries").Range("D23").Value
Address1 = Worksheets("Enquiries").Range("H20").Value
Address2 = Worksheets("Enquiries").Range("H22").Value
City = Worksheets("Enquiries").Range("H24").Value
PostCode = Worksheets("Enquiries").Range("H26").Value
StartDate = Worksheets("Enquiries").Range("D12").Value
EndDate = Worksheets("Enquiries").Range("D14").Value
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Sense check the details
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If FirstName = "" Then
MsgBox "Please put in a First name", vbInformation
Exit Sub
End If
If LastName = "" Then
MsgBox "Please put in a Last Name", vbInformation
Exit Sub
End If
If Address1 = "" Then
MsgBox " Please put in vaild address e.g. 17 Walk Down Street", vbInformation
Exit Sub
End If
If City = "" Then
MsgBox "Please enter a vaild city", vbInformation
Exit Sub
End If
If PostCode = "" Then
MsgBox "Please enter a vaild postcode e.g. P7 7AB", vbInformation
Exit Sub
End If
If StartDate = "" Then
MsgBox "Please enter the date you wish to rent the vehicle e.g. 12/02/2007", vbInformation
Exit Sub
End If
If EndDate = "" Then
MsgBox "Please enter the date you wish to return the vehicle e.g. 12/02/2007", vbInformation
Exit Sub
End If
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Find the next empty row on the Reservation sheet
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Row = 2
Do
Row = Row + 1
Reservation = Worksheets("Reservation").Range("A" & Row).Value
Loop Until IsEmpty(Reservation)
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Output the data to the Reservation sheet
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Worksheets("Reservation").Range("B" & Row).Value = FirstName
Worksheets("Reservation").Range("C" & Row).Value = LastName
Worksheets("Reservation").Range("D" & Row).Value = Address1
Worksheets("Reservation").Range("E" & Row).Value = Address2
Worksheets("Reservation").Range("F" & Row).Value = City
Worksheets("Reservation").Range("G" & Row).Value = PostCode
Worksheets("Reservation").Range("A" & Row).Value = Title
Worksheets("Reservation").Range("H" & Row).Value = StartDate
Worksheets("Reservation").Range("I" & Row).Value = EndDate
Else
MsgBox "Available.Text not recognised", vbExclamation
End If
End Sub
|
|
February 1st, 2007, 09:26 AM
|
|
Registered User
|
|
Join Date: Jan 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
They guys thanks for all your help but i have fixed the problem.
Code:
Sub Reserve_button_Click()
If Available.Text = "No" Then MsgBox "Sorry the vehicle you selected is not available", vbExclamation
flg = False
If Available.Text = "yes" Then
Title = ComboBox_Title
With Sheets("Enquiries")
FirstName = Worksheets("Enquiries").Range("D21").Value
LastName = Worksheets("Enquiries").Range("D23").Value
Address1 = Worksheets("Enquiries").Range("H20").Value
Address2 = Worksheets("Enquiries").Range("H22").Value
City = Worksheets("Enquiries").Range("H24").Value
PostCode = Worksheets("Enquiries").Range("H26").Value
StartDate = Worksheets("Enquiries").Range("D12").Value
EndDate = Worksheets("Enquiries").Range("D14").Value
End With
If FirstName = "" Then MsgBox "Please put in a First name", vbInformation: flg = True
If LastName = "" Then MsgBox "Please put in a Last Name", vbInformation: flg = True
If Address1 = "" Then MsgBox " Please put in vaild adree e.g. 17 walk down street", vbInformation: flg = True
If City = "" Then MsgBox "Please enter a vaild city", vbInformation
If PostCode = "" Then MsgBox "Please enter a vaild postcode e.g. P7 7AB", vbInformation: flg = True
If StartDate = "" Then MsgBox "Please enter the date you wish to rent the vehicle e.g. 12/02/2007", vbInformation: flg = True
If EndDate = "" Then MsgBox "Please enter the date you wish to return the vehicle e.g. 12/02/2007", vbInformation: flg = True
If flg Then Exit Sub
MyRow = 2
With Sheets("Reservation")
Do
MyRow = MyRow + 1
Reservation = Empty
Loop Until IsEmpty(Reservation)
Worksheets("Reservation").Range("B" & MyRow).Value = FirstName
Worksheets("Reservation").Range("C" & MyRow).Value = LastName
Worksheets("Reservation").Range("D" & MyRow).Value = Address1
Worksheets("Reservation").Range("E" & MyRow).Value = Address2
Worksheets("Reservation").Range("F" & MyRow).Value = City
Worksheets("Reservation").Range("G" & MyRow).Value = PostCode
Worksheets("Reservation").Range("A" & MyRow).Value = Title
Worksheets("Reservation").Range("H" & MyRow).Value = StartDate
Worksheets("Reservation").Range("I" & MyRow).Value = EndDate
End With
End If
End Sub
|
|
|
