which cell is running the macro

[taupirho]

I want to have several cells that run the same macro when left-clicked with the mouse. The first thing the macro should do is find out the value contained in the cell (or the cells address) that was clicked which started the macro. Seems easy but I'm scratching my head over this one as the obvious things like activecell and worksheet.selctionchange events don't appear to do what I want. ANyone any ideas.

 

[PHV]

Take a look at the Intersect method and the Target parameter of the event procedure.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[taupirho]

I should have been clearer. Its not the actual cell I'm clicking on to invoke the macro, but a rectangular autoshape that's overlaid on top of a cell.

 

[xlbo]

Would be faaaaaaaaar easier if you DID use a cell

Target.text
Target.value
Target.address

basically Target is a range and has all usual range properties

I think you'll find it very hard (if not impossible) to decipher which SHAPE was clicked to trigger the macro

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[taupirho]

Ok, Geoff

leads me to my next question. How do you assign a macro to a cell. Incidentally I'm using Excel 2000.

 

[xlbo]

You need to get into EVENT driven code
Essentially, every sheet has a set of events which can fire. The one you need is the SELECTION CHANGE EVENT

To see this, right click on the sheet tab you need to write the code for and choose "View code"
Select "worksheet" from the left side dropdown in the VBE and a Selection_Change sub will be auto generated. Your code would go in there

1st thing to think about is that you don't want the code to fire on EVERY selection change so you need to use the INTERSECT method to test whether your TARGET is within the set of cells that can trigger the code

Once that is done, it is easy enough to get the cell etc as TARGET has all the preperties of RANGE eg VALUE / TEXT / ADDRESS etc

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[taupirho]

Sounds promising,

Say I move to the cell using the cursor keys and then click the cell with the mouse. The selectionchange event doesn't fire right? That might be a problem.

Any thoughts?

 

[taupirho]

Found a bit of a kludgy solution to my original problem.

Providing your autoshape has text in it and the text is unique among any other autoshapes you may have , the following code snippet will return the value contained in the cell that the autoshape is overlayed on.

For Each shp In ActiveSheet.Shapes
If shp.DrawingObject.Caption = "my test caption" Then
MsgBox (shp.TopLeftCell.Cells.Cells.Value)
End If
Next

 

[xlbo]

ok - hows about the BEFORE_DOUBLECLICK event - user would need to doubleclick rather than single click but at least you'd be sure they meant to do it !

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question