+ Reply to Thread
Results 1 to 4 of 4

Capturing Shape Name/Text from OnAction property

  1. #1
    William Bartusek
    Guest

    Capturing Shape Name/Text from OnAction property

    How can I capture (assign) the text content of a shape
    (rectangle)into a procedure using the OnAction property
    for the shape?
    I have many shapes (rectangles) on a worksheet, each of
    which is named. When a shape is clicked, I need to assign
    the 'Characters.Text' content of that shape to a variable
    in the procedure called by the OnAction property. I am
    trying to create a usable 'generic' procedure rather than
    having a separate procedure for each shape.


  2. #2
    Jim Cone
    Guest

    Re: Capturing Shape Name/Text from OnAction property

    William,

    Do you have a separate procedure for each shape,
    or does each shape call the same procedure.

    Regards,
    Jim Cone
    San Francisco, USA


    "William Bartusek" <anonymous@discussions.microsoft.com> wrote in message
    news:1f5501c53ec9$9558a390$a401280a@phx.gbl...
    > How can I capture (assign) the text content of a shape
    > (rectangle)into a procedure using the OnAction property
    > for the shape?
    > I have many shapes (rectangles) on a worksheet, each of
    > which is named. When a shape is clicked, I need to assign
    > the 'Characters.Text' content of that shape to a variable
    > in the procedure called by the OnAction property. I am
    > trying to create a usable 'generic' procedure rather than
    > having a separate procedure for each shape.
    >


  3. #3
    William Bartusek
    Guest

    Re: Capturing Shape Name/Text from OnAction property

    I want to create a common procedure that captures the
    shape name that is clicked (OnAction), then places
    (assigns) the name to a variable in the called procedure.
    Right now I have accomplished this by having a separate
    procedure for each shape. See comment below.

    Private Sub Test()
    Dim txt As String, c As Range, rng As Range, rw As Long, r
    As Long, ufrm As Object

    Worksheets("SERVICE MEASURES-Strategy").Unprotect

    Worksheets("SERVICE MEASURES-Strategy").Shapes
    ("SvcOA_Age").Select 'this is the shape name that
    is 'clicked'
    'I want the "OnAction" clicked shape "Character.Text" to
    be captured in a variable in the called procedure rather
    than the pre-coded name "SvcOA_Age" (or whatever the
    Character.Text of the shape) so that I don't need a
    separate procedure for each shape.

    txt = Selection.Characters.Text
    'activate the worksheet and named range
    Worksheets("STRATEGY GUIDANCE").Activate
    Worksheets("STRATEGY GUIDANCE").Range
    ("STRATEGY_GUID").Activate
    rw = Worksheets("STRATEGY GUIDANCE").Range
    ("STRATEGY_GUID").Rows.Count
    'find the Shapes.Text in column 1 of the STRATEGY_GUID
    named range
    With Worksheets("STRATEGY GUIDANCE").Range
    ("STRATEGY_GUID").Columns(1)
    Set rng = .Find(txt, LookIn:=xlValues, LookAt:=xlWhole, _
    SearchOrder:=xlByColumns, MatchCase:=True)
    If Not rng Is Nothing Then
    r = rng.Rows.Row

    Set ufrm = SvcMeasure
    With ufrm
    .LblSvcMeasure = Worksheets("STRATEGY GUIDANCE").Range
    ("B" & r)
    .Guidance.Value = Worksheets("STRATEGY GUIDANCE").Range
    ("C" & r)
    .Strategy.Value = Worksheets("STRATEGY GUIDANCE").Range
    ("D" & r)
    .Locate = r
    .Show
    End With
    End If
    End With

    Worksheets("STRATEGY GUIDANCE").Range("A1").Activate
    Worksheets("SERVICE MEASURES-Strategy").Activate
    Worksheets("SERVICE MEASURES-Strategy").Protect

    End Sub
    >-----Original Message-----
    >William,
    >
    >Do you have a separate procedure for each shape,
    >or does each shape call the same procedure.
    >
    >Regards,
    >Jim Cone
    >San Francisco, USA
    >
    >
    >"William Bartusek" <anonymous@discussions.microsoft.com>

    wrote in message
    >news:1f5501c53ec9$9558a390$a401280a@phx.gbl...
    >> How can I capture (assign) the text content of a

    shape
    >> (rectangle)into a procedure using the OnAction property
    >> for the shape?
    >> I have many shapes (rectangles) on a worksheet, each

    of
    >> which is named. When a shape is clicked, I need to

    assign
    >> the 'Characters.Text' content of that shape to a

    variable
    >> in the procedure called by the OnAction property. I am
    >> trying to create a usable 'generic' procedure rather

    than
    >> having a separate procedure for each shape.
    >>

    >.
    >


  4. #4
    Jim Cone
    Guest

    Re: Capturing Shape Name/Text from OnAction property

    William,

    Assign the "DoThemAll" sub to each shape.
    'DoThemAll' determines the text in the shape and then
    passes that text in a variable to your main procedure (Test)...
    '---------------------------------
    Sub DoThemAll()
    Dim strName As String
    Dim strText As String
    strName = Application.Caller
    strText = ActiveSheet.Shapes(strName).TextFrame.Characters.Text
    'Call the main procedure, passing strText to it
    Test strText
    End Sub

    Private Sub Test(ByRef txt As String)
    Dim c As Range
    Dim rng As Range
    Dim rw As Long
    Dim r As Long
    Dim ufrm As Object

    'Your code
    MsgBox txt
    End Sub
    '---------------------------------

    Regards,
    Jim Cone
    San Francisco, USA



    "William Bartusek" <anonymous@discussions.microsoft.com> wrote in
    message news:05f601c53f7d$c2873570$a601280a@phx.gbl...
    > I want to create a common procedure that captures the
    > shape name that is clicked (OnAction), then places
    > (assigns) the name to a variable in the called procedure.
    > Right now I have accomplished this by having a separate
    > procedure for each shape. See comment below.
    >
    > Private Sub Test()
    > Dim txt As String, c As Range, rng As Range, rw As Long, r
    > As Long, ufrm As Object
    >
    > Worksheets("SERVICE MEASURES-Strategy").Unprotect
    >
    > Worksheets("SERVICE MEASURES-Strategy").Shapes
    > ("SvcOA_Age").Select 'this is the shape name that
    > is 'clicked'
    > 'I want the "OnAction" clicked shape "Character.Text" to
    > be captured in a variable in the called procedure rather
    > than the pre-coded name "SvcOA_Age" (or whatever the
    > Character.Text of the shape) so that I don't need a
    > separate procedure for each shape.
    >
    > txt = Selection.Characters.Text
    > 'activate the worksheet and named range
    > Worksheets("STRATEGY GUIDANCE").Activate
    > Worksheets("STRATEGY GUIDANCE").Range
    > ("STRATEGY_GUID").Activate
    > rw = Worksheets("STRATEGY GUIDANCE").Range
    > ("STRATEGY_GUID").Rows.Count
    > 'find the Shapes.Text in column 1 of the STRATEGY_GUID
    > named range
    > With Worksheets("STRATEGY GUIDANCE").Range
    > ("STRATEGY_GUID").Columns(1)
    > Set rng = .Find(txt, LookIn:=xlValues, LookAt:=xlWhole, _
    > SearchOrder:=xlByColumns, MatchCase:=True)
    > If Not rng Is Nothing Then
    > r = rng.Rows.Row
    >
    > Set ufrm = SvcMeasure
    > With ufrm
    > .LblSvcMeasure = Worksheets("STRATEGY GUIDANCE").Range
    > ("B" & r)
    > .Guidance.Value = Worksheets("STRATEGY GUIDANCE").Range
    > ("C" & r)
    > .Strategy.Value = Worksheets("STRATEGY GUIDANCE").Range
    > ("D" & r)
    > .Locate = r
    > .Show
    > End With
    > End If
    > End With
    >
    > Worksheets("STRATEGY GUIDANCE").Range("A1").Activate
    > Worksheets("SERVICE MEASURES-Strategy").Activate
    > Worksheets("SERVICE MEASURES-Strategy").Protect
    >
    > End Sub
    > >-----Original Message-----
    > >William,
    > >
    > >Do you have a separate procedure for each shape,
    > >or does each shape call the same procedure.
    > >
    > >Regards,
    > >Jim Cone
    > >San Francisco, USA



+ 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