I would like to allow prompt a user to select a shape (for example a
rectangle) Once they have done that I want to grab that shape's name and
position.

Currently what I have, prompts them to select the shape when they hit ok, it
exits from the macro. They then have to re-enter in to the macro to proceed.

I've looked into using Application.InputBox but it doesn't seem to allow the
user to select a shape on the sheet. Any help would be greatly appreciated.

I don't see any way to do this using application.inputbox.

I use xl2003.

There's an icon I can add to my favorite toolbar that allows me to select
multiple objects.

Tools|Customize|Commands tab|Drawing category
Look for "Select multiple objects"
and drag it to your favorite toolbar.

When you click on that icon, you'll get a list of objects on that activesheet
that can be selected.

You can actually invoke that same icon in code.

If you want to try this:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim mySelection As Object
Dim myCtrl As CommandBarControl
Dim TempCMDBar As CommandBar
Dim myCtrlId As Long

myCtrlId = 3990
'I'm not sure if it stays the same number in all versions and languages.

Set TempCMDBar = Nothing

'look for "select multiple objects icon
Set myCtrl = Application.CommandBars.FindControl(ID:=myCtrlId)

If myCtrl Is Nothing Then
'it wasn't found, so create a temporary toolbar to hold it
Set TempCMDBar = Application.CommandBars.Add(temporary:=True)
Set myCtrl _
= TempCMDBar.Controls.Add(Type:=msoControlButton, ID:=myCtrlId)
End If

myCtrl.Execute
Set mySelection = Selection

'clean up
If TempCMDBar Is Nothing Then
'nothing to do
Else
TempCMDBar.Delete
End If

If TypeName(mySelection) = "Range" Then
'didn't select any objects
MsgBox "a range was selected"
Else
With mySelection.ShapeRange
MsgBox .Count 'just to show you how many objects were selected
For iCtr = 1 To .Count
MsgBox .Item(iCtr).Name
Next iCtr
End With
End If

End Sub





Chad wrote:

I would like to allow prompt a user to select a shape (for example a
rectangle) Once they have done that I want to grab that shape's name and
position.

Currently what I have, prompts them to select the shape when they hit ok, it
exits from the macro. They then have to re-enter in to the macro to proceed.

I've looked into using Application.InputBox but it doesn't seem to allow the
user to select a shape on the sheet. Any help would be greatly appreciated.

--

Dave Peterson

Your observations are correct.

Work arounds would involve using two macros with the second triggered by the
selection. Use the onaction property of the rectangles to execute the second
macro.

an alternative may be to use application.Ontime to start the second after a
reasonable time to do the selection (although this is obviously not a good
way).

--
Regards,
Tom Ogilvy


"Chad" wrote:

I would like to allow prompt a user to select a shape (for example a
rectangle) Once they have done that I want to grab that shape's name and
position.

Currently what I have, prompts them to select the shape when they hit ok, it
exits from the macro. They then have to re-enter in to the macro to proceed.

I've looked into using Application.InputBox but it doesn't seem to allow the
user to select a shape on the sheet. Any help would be greatly appreciated.

Tom and Dave,

Thanks so much for the help. I think I can make one of these options work.

That is certainly an innovative solution!

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote:

I don't see any way to do this using application.inputbox.

I use xl2003.

There's an icon I can add to my favorite toolbar that allows me to select
multiple objects.

Tools|Customize|Commands tab|Drawing category
Look for "Select multiple objects"
and drag it to your favorite toolbar.

When you click on that icon, you'll get a list of objects on that activesheet
that can be selected.

You can actually invoke that same icon in code.

If you want to try this:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim mySelection As Object
Dim myCtrl As CommandBarControl
Dim TempCMDBar As CommandBar
Dim myCtrlId As Long

myCtrlId = 3990
'I'm not sure if it stays the same number in all versions and languages.

Set TempCMDBar = Nothing

'look for "select multiple objects icon
Set myCtrl = Application.CommandBars.FindControl(ID:=myCtrlId)

If myCtrl Is Nothing Then
'it wasn't found, so create a temporary toolbar to hold it
Set TempCMDBar = Application.CommandBars.Add(temporary:=True)
Set myCtrl _
= TempCMDBar.Controls.Add(Type:=msoControlButton, ID:=myCtrlId)
End If

myCtrl.Execute
Set mySelection = Selection

'clean up
If TempCMDBar Is Nothing Then
'nothing to do
Else
TempCMDBar.Delete
End If

If TypeName(mySelection) = "Range" Then
'didn't select any objects
MsgBox "a range was selected"
Else
With mySelection.ShapeRange
MsgBox .Count 'just to show you how many objects were selected
For iCtr = 1 To .Count
MsgBox .Item(iCtr).Name
Next iCtr
End With
End If

End Sub





Chad wrote:

I would like to allow prompt a user to select a shape (for example a
rectangle) Once they have done that I want to grab that shape's name and
position.

Currently what I have, prompts them to select the shape when they hit ok, it
exits from the macro. They then have to re-enter in to the macro to proceed.

I've looked into using Application.InputBox but it doesn't seem to allow the
user to select a shape on the sheet. Any help would be greatly appreciated.

--

Dave Peterson

And it just may work <vbg.

Tom Ogilvy wrote:

That is certainly an innovative solution!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote:

I don't see any way to do this using application.inputbox.

I use xl2003.

There's an icon I can add to my favorite toolbar that allows me to select
multiple objects.

Tools|Customize|Commands tab|Drawing category
Look for "Select multiple objects"
and drag it to your favorite toolbar.

When you click on that icon, you'll get a list of objects on that activesheet
that can be selected.

You can actually invoke that same icon in code.

If you want to try this:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim mySelection As Object
Dim myCtrl As CommandBarControl
Dim TempCMDBar As CommandBar
Dim myCtrlId As Long

myCtrlId = 3990
'I'm not sure if it stays the same number in all versions and languages.

Set TempCMDBar = Nothing

'look for "select multiple objects icon
Set myCtrl = Application.CommandBars.FindControl(ID:=myCtrlId)

If myCtrl Is Nothing Then
'it wasn't found, so create a temporary toolbar to hold it
Set TempCMDBar = Application.CommandBars.Add(temporary:=True)
Set myCtrl _
= TempCMDBar.Controls.Add(Type:=msoControlButton, ID:=myCtrlId)
End If

myCtrl.Execute
Set mySelection = Selection

'clean up
If TempCMDBar Is Nothing Then
'nothing to do
Else
TempCMDBar.Delete
End If

If TypeName(mySelection) = "Range" Then
'didn't select any objects
MsgBox "a range was selected"
Else
With mySelection.ShapeRange
MsgBox .Count 'just to show you how many objects were selected
For iCtr = 1 To .Count
MsgBox .Item(iCtr).Name
Next iCtr
End With
End If

End Sub





Chad wrote:

I would like to allow prompt a user to select a shape (for example a
rectangle) Once they have done that I want to grab that shape's name and
position.

Currently what I have, prompts them to select the shape when they hit ok, it
exits from the macro. They then have to re-enter in to the macro to proceed.

I've looked into using Application.InputBox but it doesn't seem to allow the
user to select a shape on the sheet. Any help would be greatly appreciated.

--

Dave Peterson


--

Dave Peterson