+ Reply to Thread
Results 1 to 7 of 7

how to select a shape

  1. #1
    EXCEL NEWS
    Guest

    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





  2. #2
    EXCEL NEWS
    Guest

    Re: how to select a shape

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

    "EXCEL NEWS" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >
    >
    >
    >



  3. #3
    Chip Pearson
    Guest

    Re: 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 NEWS" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >
    >
    >
    >




  4. #4
    EXCEL NEWS
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 NEWS" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    > >
    > >
    > >
    > >

    >
    >



  5. #5
    Dave Peterson
    Guest

    Re: 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 NEWS 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

  6. #6
    Dave Peterson
    Guest

    Re: 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 NEWS 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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 NEWS" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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

  7. #7
    EXCEL NEWS
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 NEWS 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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1