VBA Change event when selecting a shape

[Formula11]

Is there a change event in VBA each time a different shape is selected?

The change event would be while a useform is active.

The shapes are Oval and Rectangle.
Each time I select a shape, the useform would display the Height and Width.

The change event is what I was looking for, it's possible that is.

 

[Jon Peltier]

There is no such event that fires when a shape is selected. ChartObjects (the shapes that contain charts) do have some events available to them, but this won't help if you need other shapes to respond as well.

What you could do is assign a macro to each shape. The following is a simple macro that might help get you started. It belongs in a regular code module.

Sub Shape_Click()
  Dim CalledBy As Variant
  CalledBy = Application.Caller
  Dim CalledShape As Shape
  Set CalledShape = ActiveSheet.Shapes(CalledBy)
  With CalledShape
    MsgBox .Name & ": " & .Width & " wide x " & .Height & " high"
  End With
End Sub

Instead of the MsgBox, you would pass information into the UserForm.

You could use a macro to automatically assign the macro to shapes on any sheet when it is activated and unassign the macro when the sheet is deactivated. This code goes into the ThisWorkbook module:

Private Sub Workbook_SheetActivate(ByVal Sht As Object)
  Dim Shp As Shape
  For Each Shp In Sht.Shapes
    Shp.OnAction = "Shape_Click"
  Next
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sht As Object)
  Dim Shp As Shape
  For Each Shp In Sht.Shapes
    Shp.OnAction = ""
  Next
End Sub

You can include code that only assigns the macro if the UserForm is loaded.

 

[Micron]

Shapes have click event, so use that? Don't know what you mean by 'while a useform is active, but I suspect you mean that it has been loaded/initialized. It cannot be the active object if you're wanting to run an event upon clicking a shape.
EDIT - late to the party, again. Correction though, I meant rectangle as an example of a shape, not the Shape class itself.

 

[Formula11]

Thanks for the responses.

Jon Peltier, this looks pretty good and I will look into it.

Micron, these are shapes in the worksheet, not in the userform. Is the click event still available?

 

[Micron]

Micron, these are shapes in the worksheet, not in the userform. Is the click event still available?

Sorry I wasn't clear. By saying that the userform could not also be the active object at the same time I was assuming the shape was on a sheet. So I'm saying you can test whether or not your userform is loaded when the click event occurs. What I find odd is that the Shape class doesn't have a click event (AFAIK) yet a shape added from the shapes menu on the ribbon does. It becomes a star, rectangle, or whatever you added, and you can assign a click event to it. Perhaps I should call that sub, macro or procedure and not an event, but the sub ends with _Click() and does respond to being clicked on, so...

 

[Formula11]

OK thanks but I can't follow.
I just created a rectangle called "Rectangle 1" (I changed the name to "Rectangle_1" to remove space).

The sub below does nothing though. Tried code in the userform, and worksheet.

Private Sub Rectangle_1_Click()
    MsgBox "Clicked"
End Sub

 

[Micron]

Tried code in the userform, and worksheet.

So you moved the code then? Or perhaps you copied/pasted from here? It has to be in a standard module. That is where it was created when you selected the shape in design mode and chose 'assign macro' and in the assign macro dialog you accepted the default name for the event? If not, that's how I did it. If you just paste the code in a standard module first, I suspect you'll find the name of your sub in the assign macro list and should be able to choose that. You cannot have the code in userform or sheet module.

 

[Formula11]

Sorry but I'm getting confused again.

I did manage to get Jon Peltier's code to work, after figuring out it was after changing sheets.

As a question, could the code be run when a shape is inserted. Is the "insert object" action considered a change event?

 

[Jon Peltier]

What I find odd is that the Shape class doesn't have a click event (AFAIK) yet a shape added from the shapes menu on the ribbon does. It becomes a star, rectangle, or whatever you added, and you can assign a click event to it.

No, a shape has no click event, however it is added. But as I showed in my earlier reply, any shape (including a chart) can have a macro (not a click event) assigned to it.

 

[Jon Peltier]

As a question, could the code be run when a shape is inserted. Is the "insert object" action considered a change event?

There are no events associated with non-chart shapes.

What is it you're trying to accomplish? Instead of kludging together OnAction procedures, there might be better ways to get there.