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.

Please Login or Register  to view this content.

**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.

Please Login or Register  to view this content.

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?

Please Login or Register  to view this content.

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

**K-Rod** · 04-22-2008, 05:18 PM

Hi Leith,

Sorry about this, but I've no idea how to use Visual Basic

How do I zip my workbook?

Your help is much appreciated.

K-Rod.

**Leith Ross** · 04-22-2008, 10:43 PM

Hello K-Rod,

To zip your workbook, you will need a program like WinZip, PKZip, or Stuffit. Click the program name to visit the web site.

To open the Visual Basic Editor or VBE, press the keys ALT+F11 after you have opened your workbook. This was in my post on how to install a Workbook Event macro.

Sincerely,
Leith Ross