how to select a shape
 

hi,

i often use application.inputbox like

Set mycell1 = Application.InputBox(prompt:="", Type:=8)

in order to get a range,

but it failed to let me select a shape, i mean a shape object, that is a
part of drawing i cilpped and paseted into a sheet,

how to do this,

thanks a lot

how to select a shape
 

of course ,i mean how to select a shape in vba,

"EXCEL$B!!(BNEWS" wrote in message
...
hi,

i often use application.inputbox like

Set mycell1 = Application.InputBox(prompt:="", Type:=8)

in order to get a range,

but it failed to let me select a shape, i mean a shape object, that is a
part of drawing i cilpped and paseted into a sheet,

how to do this,

thanks a lot

how to select a shape
 

I don't think you can select a shape using the mouse from VBA
code. Application.InputBox certainly won't do it. I tried to do
it with a modeless userform, but apparently Excel prohibits
selecting anything in the drawing layer.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"EXCEL$B!!(BNEWS" wrote in message
...
hi,

i often use application.inputbox like

Set mycell1 = Application.InputBox(prompt:="", Type:=8)

in order to get a range,

but it failed to let me select a shape, i mean a shape object,
that is a
part of drawing i cilpped and paseted into a sheet,

how to do this,

thanks a lot

how to select a shape
 

thanks for your answer

but i mean i just want to select a shape which is within a excel sheet,
instead of selecting a drawing scope in a cad area,
i dont know the meaning of "selecting anything in the drawing lay"

i think there should be a method to let me do it, in vba,

what does modeless userform mean, would you please give me any hint further,

thanks a lot




"Chip Pearson" wrote in message
...
I don't think you can select a shape using the mouse from VBA
code. Application.InputBox certainly won't do it. I tried to do
it with a modeless userform, but apparently Excel prohibits
selecting anything in the drawing layer.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"EXCEL$B!!(BNEWS" wrote in message
...
hi,

i often use application.inputbox like

Set mycell1 = Application.InputBox(prompt:="", Type:=8)

in order to get a range,

but it failed to let me select a shape, i mean a shape object,
that is a
part of drawing i cilpped and paseted into a sheet,

how to do this,

thanks a lot

how to select a shape
 

There's a button that you can add to your favorite toolbar that pops up a dialog
that allows you to select a shape based on the names of the shapes on that
worksheet.

Tools|Customize|Commands tab|Drawing toolbar
Look for Select Multiple Objects in the commands list.

Maybe you could invoke that dialog in your code:

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

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)

'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.

EXCEL$B!!(BNEWS wrote:

hi,

i often use application.inputbox like

Set mycell1 = Application.InputBox(prompt:="", Type:=8)

in order to get a range,

but it failed to let me select a shape, i mean a shape object, that is a
part of drawing i cilpped and paseted into a sheet,

how to do this,

thanks a lot

--

Dave Peterson

how to select a shape
 

Pictures/shapes/buttons/controls don't live on the worksheet itself. They live
on a layer that floats over that worksheet (kind of like the different layers in
AutoCad).

That layer that floats over the worksheet is the excel's drawing layer.

A modeless userform is a userform that doesn't take control of the application.

If you click on File|SaveAs, you'll notice that you can't do anything else while
that dialog is showing.

If you click on Edit|Find (in xl2002+), you'll see that you can click on other
cells and continue to work while that dialog is still showing.



EXCEL$B!!(BNEWS wrote:

thanks for your answer

but i mean i just want to select a shape which is within a excel sheet,
instead of selecting a drawing scope in a cad area,
i dont know the meaning of "selecting anything in the drawing lay"

i think there should be a method to let me do it, in vba,

what does modeless userform mean, would you please give me any hint further,

thanks a lot

"Chip Pearson" wrote in message
...
I don't think you can select a shape using the mouse from VBA
code. Application.InputBox certainly won't do it. I tried to do
it with a modeless userform, but apparently Excel prohibits
selecting anything in the drawing layer.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"EXCEL$B!!(BNEWS" wrote in message
...
hi,

i often use application.inputbox like

Set mycell1 = Application.InputBox(prompt:="", Type:=8)

in order to get a range,

but it failed to let me select a shape, i mean a shape object,
that is a
part of drawing i cilpped and paseted into a sheet,

how to do this,

thanks a lot







--

Dave Peterson

how to select a shape
 

thanks a lot and thanks for your code
maybe as you said , to let the users to select the object before the code
starts is the best way ,

thanks


"Dave Peterson" wrote in message
...
There's a button that you can add to your favorite toolbar that pops up a
dialog
that allows you to select a shape based on the names of the shapes on that
worksheet.

Tools|Customize|Commands tab|Drawing toolbar
Look for Select Multiple Objects in the commands list.

Maybe you could invoke that dialog in your code:

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

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)

'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.

EXCEL$B!!(BNEWS wrote:

hi,

i often use application.inputbox like

Set mycell1 = Application.InputBox(prompt:="", Type:=8)

in order to get a range,

but it failed to let me select a shape, i mean a shape object, that is a
part of drawing i cilpped and paseted into a sheet,

how to do this,

thanks a lot

--

Dave Peterson