How to trigger a selection change between a cell and a shape

[Gerold Kriechbaumer]

Hi NG,

I have an Excel 2000 workbook with a worksheet containing one shape which
actually is a picture.

My question: How to trigger a selection change between a cell and a shape?

1. Let's say cell "A1" is selected
2. A user then selects the shape (picture), but this does not trigger the
event "Worksheet_SelectionChange"
3. After selecting the shape the user selects cell "A1" again, but this does
not trigger the event "Worksheet_SelectionChange"
4. The user selects cell "A2", this triggers the event
"Worksheet_SelectionChange"

Does anybody know how to trigger the selection change between a cell and a
shape and between a shape and the previously selected cell?

To know the selection change from a cell to a shape is required to disable
some commands (resulting from my COM-Add-In) if a shape is selected, and
therefore avoiding a message box in many different languages that the
command cannot be executed because a shape is selected.

Thanks in advance.

Gary




---
avast! Antivirus: Ausgehende Nachricht sauber.
Virus-Datenbank (VPS): 0628-1, 10.07.2006
Getestet um: 10.07.2006 21:23:17
avast! - copyright (c) 1988-2006 ALWIL Software.
http://www.avast.com

 

[Guest]

Gerold,

Run the following macro to initialize all the pictures so they have an
"OnAction" tag. Then insert the "pic_click" sub into a module.


Sub initialize()
Dim pic As Shape
For Each pic In Shapes
If pic.Type = msoPicture Then pic.OnAction = "pic_click"
Next pic
End Sub



Sub pic_click()
MsgBox ("A picture has been selected") ' ... or your code here
End Sub

Mike

 

[Gerold Kriechbaumer]

Hi Mike,

thank you for your solution.

But unfortunately I cannot use this solution, because I should not change
properties of any existing shape(s), because these shapes are not my shapes.
The picture-shape was just an example to explain the problem.

Because my program will be a COM-Add-In which has to work with any worksheet
configuration the solution must work with ANY shape selected without
changing any property.

So actually I need a solution to trigger that a cell has not been selected.

With

If TypeName(Selection) <> "Range"

I know that the selection does not represent a cell but anything else (e.g.
picture, textbox, ...), but I need to trigger that this situation has
occured, in order to disable some commands avoiding the usage of message
boxes in different languages. For example, if a user selects a shape, Excel
itself triggers that a shape has been selected, by disabling many commands
in the Format-Toolbar, so that the user cannot select it, e.g. make a shape
bold or italic. But how does Excel this trigger, and how can I this trigger
too?

Thank you for four help.

Gary

Gerold,

Run the following macro to initialize all the pictures so they have an
"OnAction" tag. Then insert the "pic_click" sub into a module.


Sub initialize()
Dim pic As Shape
For Each pic In Shapes
If pic.Type = msoPicture Then pic.OnAction = "pic_click"
Next pic
End Sub



Sub pic_click()
MsgBox ("A picture has been selected") ' ... or your code here
End Sub

Mike




---
avast! Antivirus: Eingehende Nachricht sauber.
Virus-Datenbank (VPS): 0628-1, 10.07.2006
Getestet um: 11.07.2006 08:12:29
avast! - copyright (c) 1988-2006 ALWIL Software.
http://www.avast.com

---
avast! Antivirus: Ausgehende Nachricht sauber.
Virus-Datenbank (VPS): 0628-1, 10.07.2006
Getestet um: 11.07.2006 08:50:56
avast! - copyright (c) 1988-2006 ALWIL Software.
http://www.avast.com

 

[Peter T]

Hi Gary,

Unfortunately selecting a shape does not trigger any event you can trap.
Also if a shape is selected then user re-selects the previous cell selection
no selection event is triggered.

IOW nothing will tell you of these selection changes. No doubt events are
triggered internally but Excel keeps that to itself.

It means you need to go and check the selection type, when/how often would
depend on a combination of things. The simplest approach is to check the
selection before doing anything, possibly informing user cannot perform
action with current selection, or perhaps programmatically selecting the
previous cell selection before continuing.

Regards,
Peter T