Run a macro when clicking a cell.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kevroden@googlemail.com - 28 Apr 2008 22:12 GMT

Hi,

I'm sure this has been discussed before but I'm a complete novice on
Excel so any help is appreciated.

I've created a Calendar Control and can call the macro up either by
clicking a button or right-clicking the mouse. When I click on a date
in the Calendar, the date populates the selected cell. However, what
I'd also like to do is to run the macro when I click on a specific
cell.

I've seen another post which said copy the cell and paste the picture
then you can assign the macro to the cell but the date populates the
cell you clicked on prior to the macro running so that's no good.

Can anyone advise either how to run the macro by clicking the cell so
that the date populates the clicked cell, or maybe a double-click
which I've seen in another post.

I can email my workbook to anyone to have a look if required.

Kind regards.

K-Rod.

Reply to this Message

Gary''s Student - 28 Apr 2008 22:59 GMT

You can set up a cell to call a macro when it is clicked, but there is a
problem. The macro will be called even if you run across the cell with the
arrow keys or ENTER key. That is why a double click is usually reccommended.
Try this worksheet event macro:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Set t = Target
Set a1 = Range("A1")
If Intersect(t, a1) Is Nothing Then Exit Sub
Cancel = True
Call hello
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Signature

Gary''s Student - gsnu2007h

> Hi,
>
[quoted text clipped - 20 lines]
>
> K-Rod.

Reply to this Message

kevroden@googlemail.com - 30 Apr 2008 17:57 GMT

On 28 Apr, 22:59, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:

> You can set up a cell to call a macro when it is clicked, but there is a
> problem. The macro will be called even if you run across the cell with the
[quoted text clipped - 64 lines]
>
> - Show quoted text -

Hi,

Thanks for the responses.

I can't get either of these to work though.

When I double-click a cell I get the error "Compile error. Sub or
function not defined".

Do I need to link this into my calendar in some way?

Thanks for your help.

K-Rod.

Reply to this Message

Gord Dibben - 01 May 2008 00:35 GMT

Did you see my post with the macro and the link to Ron de Bruin's site?

If so, have you gone to Ron's site for info?

Gord Dibben MS Excel MVP

>I can't get either of these to work though.
>
[quoted text clipped - 6 lines]
>
>K-Rod.

Reply to this Message

kevroden@googlemail.com - 03 May 2008 10:59 GMT

> Did you see my post with the macro and the link to Ron de Bruin's site?
>
[quoted text clipped - 14 lines]
>
> - Show quoted text -

Hi,

Yes I visited the link and copied the code but get a runtime error.

Can anyone help out? I can send my workbook via email if someone
would be so kind as to have a look at it? As I mentioned, I know
nothing about VB and just want to get this calendar working.

Thanks,

K-Rod.

Reply to this Message

Gord Dibben - 03 May 2008 18:09 GMT

See here for step by step instructions.

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

If you scroll to the page bottom, there are downloads available.

"Runime" error could arise for a number of reasons.

If you want you can upload the workbook to one of these sites then post back
with the URL

http://www.freefilehosting.net/
http://savefile.com/

Gord

>Hi,
>
[quoted text clipped - 7 lines]
>
>K-Rod.

Reply to this Message

kevroden@googlemail.com - 05 May 2008 21:05 GMT

> See here for step by step instructions.
>
[quoted text clipped - 24 lines]
>
> - Show quoted text -

Hi Gord,

Thanks for getting back to me.

I've followed those step by step instructions and I can get the
calendar to pop up either with right-click or button. What I want to
do now is have it pop up with a double-click on a particular cell, or
cells.

I've uploaded the workbook here as advised.

http://www.freefilehosting.net/download/3gk65

Thanks again.

K-Rod.

Reply to this Message

Gord Dibben - 05 May 2008 23:41 GMT

All you uploaded was a Class Module.........*.cls

Using the the Excel Calendar Workbook from Martin's site.

Right-click on Sheet1 tab and "View Code"

Copy/paste this into that sheet module.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
OpenCalendar
Cancel = True
End Sub

Alt + q to return to the Excel window.

Double-click on any cell.

Gord

>Hi Gord,
>
[quoted text clipped - 12 lines]
>
>K-Rod.

Reply to this Message

kevroden@googlemail.com - 07 May 2008 08:58 GMT

> All you uploaded was a Class Module.........*.cls
>
[quoted text clipped - 34 lines]
>
> - Show quoted text -

Hi Gord,

I'm still getting the error.

How do I upload the full workbook please?

Thanks,

K-Rod.

Reply to this Message

Gord Dibben - 28 Apr 2008 23:06 GMT

See how Ron de Bruin does it.

http://www.rondebruin.nl/calendar.htm

After installing control and code, just click on any cell to pop up the Calendar
and insert the date you pick.

If you just want today's date use this event code without the Calendar.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Target.Value = Format(Date, "dd-mmm-yy")
Cancel = True
End Sub

Gord Dibben MS Excel MVP

>Hi,
>
[quoted text clipped - 20 lines]
>
>K-Rod.

Reply to this Message