Calendar Date Entry

[Guest]

Does anyone know how I can get Excel to give me a dropdown calendar to choose from when I want to enter a date?

 

[KniteMare]

Open excel
Right click on the little picture of the excel page in the upper left corner
At the bottom choose view code
If the project window is not visable on the left side go to the top of the screen and anywhere in the grey right click and make sure Standard is checked
Hover the mouse over the icons till you find the Project explorer, click this
Right click in the Project explorer and chose insert
then choose UserForm
Double click on userform
On the toolbox that just popped up right click and choose additional controls
Go find Calendar control 8.0
If it is not there go to the top of the screen and click on Tools
Then References
Then go find Microsoft excel 8.0 object library
now go back to your userform do the right click thing again and find the control Calendar 8.0
Drag if, drop it on your form and size it to fit, leave room near the bottom for a finished button
place a button on your form and lable it Finished
Right click on your form choose View code and place this code in the window that pops up

Private Sub Calendar1_Click()
Dim Dy, Dt
Dy = Calendar1.Value
Dt = Calendar1.Value
Dy = Format(Dy, "dddd")
Dt = Format(Dt, "mmmm-dd-yyyy")

Range("D1") = Dy
Range("I1") = Dt

End Sub
Private Sub ExitCalendar_Click()
Unload UserForm1
End Sub
Private Sub UserForm_Initialize()
Me.Calendar1.Today
End Sub

On the sheet your form will appear on create a button and call it Calendar

Right click it choose view code and past this code there

Public Sub Calendar_Click()
Load UserForm1
UserForm1.Show
End Sub

Ta Da A calendar that sets to today upon open and will place the day and date on your sheet.

 

[Ivan F Moala]

Not to take away from Kightmares good
efforts....BUT you can also place this control on a worksheet via the control toolbox > Morecontrols Button.


Ivan

 

[Dave Hawley]

Sorry for hijacking your question.


Ivan, I have never tried that! Does work in the same way as having it on a UsereForm?



Ok, cool loks like it does ) My next question is can I make it stay on the Control toolbar? It only seems to be available under "Additional controls" (



_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-03-12 19:45

 

[Ivan F Moala]

Ivan, I have never tried that! Does work in the same way as having it on a UsereForm?



Ok, cool loks like it does ) My next question is can I make it stay on the Control toolbar? It only seems to be available under "Additional controls" (



_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-03-12 19:45

Thats a good Question Dave...never really thought about making it stay on a comm bar?
But yes your right it doesn't.....should look
@ commands behind this...

Ivan

 

[Guest]

Thanks very much for your help!

On 2002-03-12 19:04, KniteMare wrote:
Open excel
Right click on the little picture of the excel page in the upper left corner
At the bottom choose view code
If the project window is not visable on the left side go to the top of the screen and anywhere in the grey right click and make sure Standard is checked
Hover the mouse over the icons till you find the Project explorer, click this
Right click in the Project explorer and chose insert
then choose UserForm
Double click on userform
On the toolbox that just popped up right click and choose additional controls
Go find Calendar control 8.0
If it is not there go to the top of the screen and click on Tools
Then References
Then go find Microsoft excel 8.0 object library
now go back to your userform do the right click thing again and find the control Calendar 8.0
Drag if, drop it on your form and size it to fit, leave room near the bottom for a finished button
place a button on your form and lable it Finished
Right click on your form choose View code and place this code in the window that pops up

Private Sub Calendar1_Click()
Dim Dy, Dt
Dy = Calendar1.Value
Dt = Calendar1.Value
Dy = Format(Dy, "dddd")
Dt = Format(Dt, "mmmm-dd-yyyy")

Range("D1") = Dy
Range("I1") = Dt

End Sub
Private Sub ExitCalendar_Click()
Unload UserForm1
End Sub
Private Sub UserForm_Initialize()
Me.Calendar1.Today
End Sub

On the sheet your form will appear on create a button and call it Calendar

Right click it choose view code and past this code there

Public Sub Calendar_Click()
Load UserForm1
UserForm1.Show
End Sub

Ta Da A calendar that sets to today upon open and will place the day and date on your sheet.