Student1000
05-15-2010, 09:19 AM
Hello, I am new to VBA. Currently I am creating a booking website. One UserForm requires the user to enter the Arrival and Departure Date. For this I used the Calendar from the Toolbox.
Now I have two problems:
1st. how do I make sure that the date that was chosen as arrival date will be set as default date in the 2nd calendar? And how can I make sure that the date entered in the second (departure Calendar) is later than the one in the arrival calendar?
2nd. can someone help me with my code regarding the SpinButton2? It does not just add one week but 3 weeks. only after using the "reset to todays date" CommandButton it works Properly.
3rd. If I click on the departure calendar to manually select a date and then use the SpinButton1 to add further dates, it always starts at the current date.
It would be great if someone can help me with that. thx
p.s. I'm using Excel 2007 and write the VBA code with the editor (ALT+F11)
Option Explicit
Dim dDate As Date 'define dDate as Date Variable
'############ Arrival Calendar #############
Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
Me.Calendar2.Value = Date 'sets the current date as default date
End Sub
Private Sub Calendar2_Click()
Sheets("User Data").Select
Range("D23").Select
ActiveCell = Calendar2.Value 'by clicking on Calendar 2 arrival date is entered into the User Data Sheet
End Sub
Private Sub CommandButton2_Click()
Calendar2 = Date 'resets the arrival calendar's date to default
End Sub
'######### Departure Calendar #############
Private Sub Calendar1_Click()
Sheets("User Data").Select
Range("D24").Select
ActiveCell = Calendar1.Value 'by clicking on Calendar 1 Departure date is entered into the User Data Sheet
End Sub
Private Sub CommandButton1_Click() 'set all values to 0/default
Calendar1 = Date 'defaults date
SpinButton1 = 0 'defaults Spinbutton to 0
SpinButton2 = 0
Label6.Caption = 0 'defaults label to 0
Label7.Caption = 0
End Sub
Private Sub SpinButton1_Change()
Sheets("User Data").Select 'makes sure that the selected date is entered into the right cell in the User Data sheet
Range("D24").Select 'Otherwise the cell of calendar 2 (arrival) would be selected
ActiveCell = Calendar1.Value
If SpinButton1 = 0 Then
Calendar1 = dDate
Else
Label6.Caption = SpinButton1 'assigns number of additional days (selected by SpinButton) to Textboox
Calendar1 = DateAdd _
("d", Label6.Caption, Now()) 'Now() function for counting the date as of today
End If '"d" indicates that the day value will be changed, label6.caption will be added to the current date (Now())
UpdateCell
End Sub
Private Sub SpinButton2_Change()
Sheets("User Data").Select
Range("D24").Select
ActiveCell = Calendar1.Value
If SpinButton2 = 0 Then
dDate = Calendar1
Else
Label7.Caption = SpinButton2
Calendar1 = DateAdd("d", (Label7.Caption * 7), dDate)
End If
UpdateCell
End Sub
Private Sub UpdateCell()
ActiveCell = Calendar1
ActiveCell.NumberFormat _
= "dddd d mmmm yyyy" 'enters the selected date in the format DD.MM.YYYY into the excel cell
End Sub
'####################### can someone help me here?
Private Sub CommandButton3_Click()
If Calendar1.Value <= Calendar2.Value Then
MsgBox ("Your Departure date must be after the arrival date")
Else
Calendar.Hide
UserForm5.Show
End If
End Sub
Now I have two problems:
1st. how do I make sure that the date that was chosen as arrival date will be set as default date in the 2nd calendar? And how can I make sure that the date entered in the second (departure Calendar) is later than the one in the arrival calendar?
2nd. can someone help me with my code regarding the SpinButton2? It does not just add one week but 3 weeks. only after using the "reset to todays date" CommandButton it works Properly.
3rd. If I click on the departure calendar to manually select a date and then use the SpinButton1 to add further dates, it always starts at the current date.
It would be great if someone can help me with that. thx
p.s. I'm using Excel 2007 and write the VBA code with the editor (ALT+F11)
Option Explicit
Dim dDate As Date 'define dDate as Date Variable
'############ Arrival Calendar #############
Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
Me.Calendar2.Value = Date 'sets the current date as default date
End Sub
Private Sub Calendar2_Click()
Sheets("User Data").Select
Range("D23").Select
ActiveCell = Calendar2.Value 'by clicking on Calendar 2 arrival date is entered into the User Data Sheet
End Sub
Private Sub CommandButton2_Click()
Calendar2 = Date 'resets the arrival calendar's date to default
End Sub
'######### Departure Calendar #############
Private Sub Calendar1_Click()
Sheets("User Data").Select
Range("D24").Select
ActiveCell = Calendar1.Value 'by clicking on Calendar 1 Departure date is entered into the User Data Sheet
End Sub
Private Sub CommandButton1_Click() 'set all values to 0/default
Calendar1 = Date 'defaults date
SpinButton1 = 0 'defaults Spinbutton to 0
SpinButton2 = 0
Label6.Caption = 0 'defaults label to 0
Label7.Caption = 0
End Sub
Private Sub SpinButton1_Change()
Sheets("User Data").Select 'makes sure that the selected date is entered into the right cell in the User Data sheet
Range("D24").Select 'Otherwise the cell of calendar 2 (arrival) would be selected
ActiveCell = Calendar1.Value
If SpinButton1 = 0 Then
Calendar1 = dDate
Else
Label6.Caption = SpinButton1 'assigns number of additional days (selected by SpinButton) to Textboox
Calendar1 = DateAdd _
("d", Label6.Caption, Now()) 'Now() function for counting the date as of today
End If '"d" indicates that the day value will be changed, label6.caption will be added to the current date (Now())
UpdateCell
End Sub
Private Sub SpinButton2_Change()
Sheets("User Data").Select
Range("D24").Select
ActiveCell = Calendar1.Value
If SpinButton2 = 0 Then
dDate = Calendar1
Else
Label7.Caption = SpinButton2
Calendar1 = DateAdd("d", (Label7.Caption * 7), dDate)
End If
UpdateCell
End Sub
Private Sub UpdateCell()
ActiveCell = Calendar1
ActiveCell.NumberFormat _
= "dddd d mmmm yyyy" 'enters the selected date in the format DD.MM.YYYY into the excel cell
End Sub
'####################### can someone help me here?
Private Sub CommandButton3_Click()
If Calendar1.Value <= Calendar2.Value Then
MsgBox ("Your Departure date must be after the arrival date")
Else
Calendar.Hide
UserForm5.Show
End If
End Sub