VBA Calendar

[Student1000]

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

[Bob Phillips]

Post your workbook, it is far too much effort to try and recreate it.

[mdmackillop]

Is this an assignment?

[Student1000]

Here is my workbook. thx in advance!

(Note: the "User Data" sheet is for storing calendar values')

[Student1000]

Is this an assignment?
I'm trying to teach myself vba. it's supposed to be helpful...

[Bob Phillips]

I would suggest these chnages



Private Sub Calendar2_Click()
Sheets("User Data").Range("D23").Value2 = Calendar2.Value 'by clicking on Calendar 2 arrival date is entered into the User Data Sheet

Calendar1.Value = Calendar2.Value
End Sub

Private Sub SpinButton1_Change()

Sheets("User Data").Range("D24").Value2 = Calendar1.Value
Label6.Caption = SpinButton1 'assigns number of additional days (selected by SpinButton) to Textboox
Calendar1 = DateAdd("d", Label6.Caption, Calendar2.Value)

UpdateCell
End Sub

Private Sub SpinButton2_Change()

Sheets("User Data").Range("D24").Value2 = Calendar1.Value
Label7.Caption = SpinButton2
Calendar1 = DateAdd("d", (Label7.Caption * 7), Calendar2.Value)

UpdateCell
End Sub

[Student1000]

Hey xld, thx a lot it works perfect.

Could you maybe also tell me how I now make sure that The user cannot click a date on the departure calendar that is earlier than the arrival date?
and how do I make sure that the user does not enter an arrival date that is earlier than the current date? (obviously he cannot depart yesterday :) )

thx.

[Bob Phillips]

You can't stop them as far as I am aware as it is in ActiveX control that is not exposed in that way. The best you can do is to throw a warning if they do.

[Student1000]

K thank you very much... I just give out a message...

cheers

[rbrhodes]

Could you maybe also tell me how I now make sure that The user cannot click a date on the departure calendar that is earlier than the arrival date?
and how do I make sure that the user does not enter an arrival date that is earlier than the current date? (obviously he cannot depart yesterday :) )


Can't stop them...