Solved: how do I execute a macro on a cell click

[bramanad]

Dear all,

I have written a macro and I am happy with it.
I have assign this macro to a text box and things are runnning fine.

I want to execute it when the users clicks on a given cell.
What I think I miss is something like an event call cell_click or similar

[Hoopsah]

Hi bramanad

If you right click on the tab you will be using and goto view code.

Enter something like:

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$D$10"
Call Macro1
End Select
End Sub[/VBA]

In this case when cell D10 is clicked on the macro "macro1" will be executed.

[bramanad]

hi hoopsah. thnaks for the suggestion.
what I was missing is the Worksheet_selectionChange procedure.
now I got my stuff up and running (see code below)

alternate question becomes now. How can I get access to all existing
predefiened procedure such Worksheet_selectionChange

[VBA]Sub Workbook_open()
Call Worksheet_SelectionChange
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("H:H")) Is Nothing Then
Call MyMacro
End If
End Sub[/VBA]

[phendrena]

[vba]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1:A1")) Is Nothing Then 'Enter the cell range that the user is going to clikc in here
Call MacroName 'the name of your macro
End If
End Sub[/vba]

[Bob Phillips]

You have some issuse there. Workbook code goes in the Thisworkbook code module, worksheet code goes into the worksheet code module. You cannot call from one to the other without making the procedures public, and prefix with the codemodule name.

[lucas]

Hi bramanad,
Instead of spending my time helping you I just spent some time cleaning up your mess on this forum.

You posted the identical post 3 times. You had responses in two of them.

I cannot emphasize strongly enough how inefficient this is. It is not only confusing for you but for those trying to help you.

Please only post a question one time and follow up questions in the same thread.

Extra threads deleted or merged with this one.

If you don't understand how the forum works then I would advise that you read our FAQ and spend some time trying to figure out how to post before you assail us with multiple posts with the same question in future. Thank you for your understanding.

[bramanad]

dear xld,
as it is now, the code behaves the way I want.
Now if your suggestion helps me to writte with better coding practice, I
will do it.

what can go wrong if I dont ?

[bramanad]

lucas, I am suprised, I have just clicked the "quick reply" button,
typed my text then hit "post quick reply"
I just read -quickly - the FAQ. I dont see what I have done wrong

[lucas]

Hi bramanad,
It happens I guess so please just be aware of it when/if it happens. If it happens again, contact an admin so we can delete/merge the posts.

I apologize if I jumped to conclusions.

[Bob Phillips]

dear xld,
as it is now, the code behaves the way I want.

I don't see how.

what can go wrong if I dont ?

It won't work?

[lucas]

Bob is trying to tell you that this:
[VBA]Sub Workbook_open()
Call Worksheet_SelectionChange
End Sub[/VBA]
Will not work. You cannot call the worksheet_selectionChange event from the workbookOpen event as you have in the code above.

The workbook open code must go in the thisworkbook module and the worksheet_selectionChange code must go in the code module for the sheet you want it to work on.

[bramanad]

xld and bob claim it must not be working, but it does, and all is located in a shhet code. not in "thisworkbook" section.
I just managed to make it work by respecting your suggestions
but I wonder why I should bother (except for good programming practices)

[lucas]

No, you are wrong......if you put it all in the code for the sheet then the only code that is working is the worksheet_SelectionChange code.....the other is being ignored and can be deleted.