Prompt user to select a shape

[MM User]

Hi,

You can use the InputBox to prompt the user to enter some text or range - is
it possible to also prompt the user to select a shape?

Thanks

 

[Gary''s Student]

Sub shapepicker()
x = Application.InputBox(prompt:="enter the shape name: ", Type:=2)
ActiveSheet.Shapes(x).Select
End Sub

The user would enter something like Oval 1

 

[MM User]

Apologies but I need the user to actually select the shape not enter the
name - is this possible?

thanks

 

[Gary''s Student]

If you mean by click the shape. We will use two macros:

Sub pic_click()
MsgBox (Application.Caller)
'do something else
End Sub

Sub tell_user()
MsgBox ("please click on a shape")
End Sub

Both macros go in standard modules. Right-click all the shapes and Assign
Macro...
All shapes are to be assigned the pic_click macro

1. first run tell_user. The user will first click the ok button and then
click on a shape
2. this causes the pic_click macro to run. Application.Caller tells the
user (and your VBA) exactly which shape was clicked.


Putting macros on Shapes is just as effective as putting them on Buttons and
a lot more fun!

 

[Dave Peterson]

Maybe you could show a dialog that allows the user to choose a shape (or
multiple shapes).

In xl2003, there is an icon that you can add to your favorite toolbar.

You can do it via:
tools|customize|commands tab|drawing category
look for "Select multiple objects"

In code, you can do this:

Option Explicit
Sub testme01()
Dim myCtrl As CommandBarControl
Dim myCB As CommandBar
Dim iCtr As Long

Set myCB = Nothing

Set myCtrl = Nothing
On Error Resume Next
Set myCtrl = Application.CommandBars.FindControl(ID:=3990)
On Error GoTo 0

If myCtrl Is Nothing Then
'not on any existing toolbar, so create a temporary toolbar
Set myCB = Application.CommandBars.Add(temporary:=True)
myCB.Visible = False
'add that button
Set myCtrl = myCB.Controls.Add(Type:=msoControlButton, ID:=3990)
End If

myCtrl.Execute
MsgBox TypeName(Selection)

Select Case LCase(TypeName(Selection))
Case Is = LCase("Range")
MsgBox "A range is selected"
Case Else
For iCtr = 1 To Selection.ShapeRange.Count
MsgBox Selection.ShapeRange(iCtr).Name
Next iCtr
End Select

'clean up
If myCB Is Nothing Then
'found it on an existing toolbar
Else
myCB.Delete
End If

End Sub

=========
Personally, I think I'd tell the users to select the object before the code
starts. You can test the typename() of the selection to see if they have a
Range selected.

If it is a Range, just yell at the user to select an object and start the macro
once more.