Drop-Down Box with a Calendar

[UHsoccer]

Found a reference to my problem elsewhere, I declared
"Public calendar1 as calendar"

The new code posted here brings up the calendar on a double -click, BUT there is still an error on the line with Calendar1 "Object variable or with block not set"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim Cell1 As String, Cell2 As String
    Cell1 = "$F$20" 
    Cell2 = "$F$25"  
    If Target.Address = Cell1 Or Target.Address = Cell2 Then
        Load UserForm2
        UserForm2.Show
        ActiveCell.Value = Calendar1.Value  '  <<-- Error
        UserForm2.Hide
        Unload UserForm2
    End If
    
End Sub

Thanks in advance

[TGM]

I haven't got a "calendar control" but I have used the "Microsoft Date and Time Picker Control 6.0 (SP4)" if this helps anyone.
Cheers Nimrod

[cresswell80]

Originally Posted by nanefy

Hi,

I have used the calander function below and it works perfectly. The only problem is that it doesnt, by default, bring up the current month. I implemented this in September and now in October it doesnt bring October up as the default date. Can anyone tell me how to do this?

Cheers!

Love this shortcut to calender but has anyone any ideas on nanefy's problem above???

thanks

[D4VE]

Try to add this code:

Private Sub UserForm_Activate()
Today = Now
Calendar1.Value = DateValue(Today)
End Sub

[cresswell80]

Originally Posted by D4VE

Try to add this code:

Private Sub UserForm_Activate()
Today = Now
Calendar1.Value = DateValue(Today)
End Sub

sorry - where would you put this in the original code? i'm not the best at VB..

[atmospheric]

Found this example to be simple enough and worked for me (so it must be easy!):

http://www.ozgrid.com/VBA/excel-calendar-dates.htm

Also, there's an excellent tutorial here that includes adding a toolbar button and a menu item:

http://www.fontstuff.com/vba/vbatut07.htm

[UHsoccer]

The step to put the calander on the user form (as described by Nimrod), has a problem when you want to distribute the spreadsheet to other users. You have to perform the steps shown below on each system!!!! I am expected to make my application available to about 100 unique users>

Is there another way besides the step below?

Second: Put calander on userform
1-In VBA window select the "toolbox"
2- Right click on "toolbox"
3- Select "additional controls" item from drop down menu
4- check/select "calender control" from the window that will appear
5- a calender object will appear on the "toolbox"
6- Drag and size the calender object from the "toolbox" to "userform1"

[TheGhost]

This is amazing! Thanks for the great instructions. They were perfect! (Except for my spelling)

[rmtaylor]

Thanks for sharing your knowledge.
I have been looking for this function for sometime it made my sheet more user friendly.

Robert

[kyrgan]

This is an awesome thread.
Anyone have input on how to format the date without using the cell format controls to guide the parameters of the date field?

I am looking at how to properly format the date into a variable to pass to SQL.

Gracias