Can I assign a macro to a cell?

**K-Rod** · 04-22-2008, 11:38 AM

Hi,

I'm a novice on Excel so any help, in lamens terms, is greatly appreciated.

What I'm trying to do is create a spreadsheet where the user clicks on a cell, and a calendar pops up where they can select a date. The calendar then disappears and the date populates the cell.

I've created the macro by following the instructions on the following website - http://www.fontstuff.com/vba/vbatut07.htm which works fine. I can call up the calendar either by pressing a button or right-clicking.

I'm not sure if it's possible but what I want to happen is for the calendar to pop up when the user clicks on the cell, rather for them to have to right-click or click a button. Is this possible?

Any help is appreciated.

Regards, K-Rod.

**royUK** · 04-22-2008, 11:54 AM

if you want the date in A1 then in the VB Editor double click on the sheet in the properties window and select Worksheet in the left hand drop down & make sure
Worksheet_SelectionChange is displayed in the right hand one.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address > "$A$1" Then Exit Sub
    If Target.Count <> 1 Then Exit Sub
   frmCal.Show
End Sub

**Leith Ross** · 04-22-2008, 02:02 PM

Hello K-Rod,

This routine will display the calendar on any sheet in the workbook when you user clicks the cell.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  frmCalendar.Show
End Sub

How to Save a Workbook Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

**K-Rod** · 04-22-2008, 04:08 PM

Thanks guys.

Leith - how do I copy the macro in step 1?

Today was the first time I've ever used Visual Basic and I just followed the instructions in the link in my post, I don't really understand it. As long as it does what I want it to do I'm happy.

Do I just apend the routine you wrote at the bottom of the code I've already got like this?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Insert Date").Delete
End Sub

Private Sub Workbook_Open()
    Dim NewControl As CommandBarControl
    Application.OnKey "+^{C}", "Module1.OpenCalendar"
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Insert Date").Delete
    On Error GoTo 0
    Set NewControl = Application.CommandBars("Cell").Controls.Add
    With NewControl
            .Caption = "Insert Date"
            .OnAction = "Module1.OpenCalendar"
            .BeginGroup = True
      End With
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    frmCalendar.Show
End Sub

Once this is done, can I assign this macro to selected cells only?

Many thanks.

**Leith Ross** · 04-22-2008, 04:21 PM

Hello K-Rod,

The code I posted needs to go into the ThisWorkbook module in your project. If the other code is in this module then everything should work. If not, post your workbook so I can review it. You will need to zip it before attaching it to your post.

Sincerely,
Leith Ross