Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Drop-Down Box with a Calendar

This is a discussion on Drop-Down Box with a Calendar within the Excel Questions forums, part of the Question Forums category; Is there a way to set up a designated cell in a spreadsheet with a drop-down box that will have ...

  1. #1
    TJ
    TJ is offline
    New Member
    Join Date
    Aug 2002
    Posts
    3

    Default

    Is there a way to set up a designated cell in a spreadsheet with a drop-down box that will have a calendar, so that when you click on a date in the calendar it puts that date in the cell?

  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default

    TJ:
    I think I've been able to create something that will meet your needs nicely... however it will require about 5 lines of VBA code ... is that OK ? If so continue to read.

    The Resulting Method
    ( This is the result if the VBA code and form is produced )
    When you want a date in a particular cell
    1- Click once on the cell where you would like date
    2- Press Ctrl+d ( a calender will appear)
    3- Double click on date in Calender
    4- Date is entered in selected cell
    5- Calender disappears

    HOW TO MAKE IT HAPPEN

    First: Make a VBA form
    1- Open VBA editor
    2- Goto VBA toolBar
    3- Select "Insert" from VBA toolbar
    4- Select "UserForm" from drop down menu
    (you have created a userform called "userform1"

    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"

    Third : Write VBA code for calender
    1- Once calender is on userform1 then "double click" the calender...the VBA code window for the calender will open
    2- Insert the following code
    Code:
    Private Sub Calendar1_DblClick()
    
    ActiveCell.Value = Calendar1.Value
    UserForm1.Hide
    Unload UserForm1
    End Sub
    Fourth Write code to make calender appear
    -1 In VBA window select "insert" from VBA toolbar
    -2 From drop down menu select "Module"
    -3 In the new VBA "Module" window that will appear paste the following code
    Code:
    Public Sub GetDate()
    Load UserForm1
    UserForm1.Show
    End Sub
    Fifth : Assign Shortcut key to Macro
    -1 Close VBA window with the X in upper right corner
    -2 You should now be looking at excel worksheet
    -3 On excel toolbar select "Tools"
    -4 From drop down menu select "Macro"
    -5 From next drop down select "Macros..."
    -6 From the list of macros that appear select the name "GetDate"
    -7 Select the "command button " in the same window labeled "Options"
    -8 Assign the shortcut key for Macro to be "Ctrl+d"
    -Press OK and exit window

    IT IS NOW READY FOR USE

    _________________
    NOTE: (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)
    Adieu,Nimrod
    [ This Message was edited by: Nimrod on 2002-08-02 21:44 ]

  3. #3
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,278

    Default

    Nimrod,

    It's past midnight here, and I'm surfing this board. Came across your contribution and tried it...awesome...thanks

    Brian

  4. #4
    TJ
    TJ is offline
    New Member
    Join Date
    Aug 2002
    Posts
    3

    Default

    Nimrod,

    I'll try it & let you know. Thanks Much!!

  5. #5
    TJ
    TJ is offline
    New Member
    Join Date
    Aug 2002
    Posts
    3

    Default

    Nimrod,

    Just tried it and it works great. Many thanks for your help, you've saved me invaluable time.

    TJ

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi Nimrod:

    Thanks for your contribution ... works Great!

    Regards!

    Yogi

  7. #7
    AC
    AC is offline
    Board Regular
    Join Date
    Mar 2002
    Posts
    142

    Default

    Nimrod, I finally got the calendar control to load on my computer, when the form comes up should the calendar be on today’s date? Mine is not. Thanks

  8. #8
    New Member
    Join Date
    Sep 2005
    Posts
    47

    Default Calander Problems

    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!

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Detroit, MI
    Posts
    934

    Default

    Nimrod - The instructions were very well organized and it worked on the first try

    Is there a way to double click on the cell and have the calendar form pop-up?

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Detroit, MI
    Posts
    934

    Default

    I am trying the following

    Code:
    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
            ActiveCell.Value = Calendar1.Value
            UserForm2.Hide
            Unload UserForm2
        End If
        
    End Sub
    It debugs to Calendar1 "Variable not defined".

    How does one define that?

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com