Pop-up Calendar

[00Cameron]

Can anyone tell me how to insert a pop-up Calendar within a cell that opens when you click in the cell or when you type in the first number of the date then disappears when you select a date. Much appreciated in advance.

 

[John_w]

Try the calendar date picker at Excel VBA date picker - Trevor Eyre.

Follow the instructions to import the date picker userform code into a new workbook.

To display the calendar when you select a cell (for example B6) put this code in the sheet's sheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim form As CalendarForm
    Dim d As Date

    If Target.Address = "$B$6" Then
        Set form = New CalendarForm
        d = form.GetDate(SelectedDate:=Date)
        If d > 0 Then Target.value = d
    End If

End Sub

 

[edge37]

Hi, I'm writing here hoping to get assitance (I know the question is old and I may not get assistance but...). I'm trying to use your code with Excel VBA date picker - Trevor Eyre, it works well in a new worksheet. I want to use it in my file and the sheet module already has a code I use, I try to copy your code below the one I have, but for some reason it refuses to work, it gives me a message saying: "Compile error - Ambiguous name detected: Worksheet_SelctionChange". In the pic I how you how I put it and it doesn't want to work like that. Any suggestions?

Thank you very much

 

[Joe4]

"Compile error - Ambiguous name detected: Worksheet_SelctionChange".

That means you have two different procedures in the same module with the exact same name - that is not allowed. All procedure names must be unique within a module.

Since the "Worksheet_SelectionChange" is an event procedure that fires automatically upon a range selection, if you name the second one something else, it will NOT run automatically. You have no flexibility in naming event procedures, they MUST be named a certain way in order to run automatically.

You need to combine those two procedures with the same name into one single procedure with that name.

 

[edge37]

That means you have two different procedures in the same module with the exact same name - that is not allowed. All procedure names must be unique within a module.

Since the "Worksheet_SelectionChange" is an event procedure that fires automatically upon a range selection, if you name the second one something else, it will NOT run automatically. You have no flexibility in naming event procedures, they MUST be named a certain way in order to run automatically.

You need to combine those two procedures with the same name into one single procedure with that name.

I'll try to figure it up with your info (eventhough I'm not that skilled in Excel). But, observing the pic with both codes, how would you change those for they can actually work? How do I combine them? Thank you again

 

[John_w]

It's easier to help you if you post code inside VBA tags (like my posts), not as a screenshot, then I can easily copy the code.

Combining your Worksheet_SelectionChange with mine, try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim form As CalendarForm
    Dim d As Date

    If Target.Address = "$B$6" Then
        Set form = New CalendarForm
        d = form.GetDate(SelectedDate:=Date)
        If d > 0 Then Target.value = d
    Else
        Range("AG10").value = ActiveCell.value
    End If

End Sub

But that code might be wrong because I don't know if the calendar cell (B6) should be treated in the same way as the active cell.

 

[edge37]

Thank you, I'll try it.

EDIT: You guessed right, it doesn't work. I think I have to search other options. Thanks

 

[John_w]

I didn't notice your A2 is the calendar cell, so change $B$6 in If Target.Address = "$B$6" Then in my previous code to $A$2.

If it still doesn't work describe what's meant to happen with the calendar cell and the other cells.

 

[edge37]

Hi John_w, let me report that I implemented the same formula (changing cell numbers as suggested) in another worksheet and it worked like expected. It actually works. So I think the problem is solved.. Thank you for your assitance.