Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Basalt, Colorado, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determine which text box(shape) called macro (97 )

    Hi,
    I would like to have several textboxes on a sheet calling a single macro which determines which box/shape was clicked and act accordingly.
    I realize that I could just call a different macro from each text box when clicked, but would like to have a single macro which could (for example) load a workbook which is named in the text of the shape.

    Is there a way to do this?
    Thanks,
    Dan Arbaney

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Determine which text box(shape) called macro (97 )

    Check out this from John Walkenback site. "Handle Multiple UserForm Buttons With One Subroutine"
    http://j-walk.com/ss/excel/tips/tip44.htm

    He discusses this and has an example worksheet that you may download.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Determine which text box(shape) called macro (97 )

    Steve is exactly correct, John shows the way to do it, but I wish John would have given more examples.
    In your case, I think that you will have to use the textboxes from the control toolbox. You will probably want them to be multiline, so click the properties button on the control toolbox and change the Multiline property to true. You also probably want to change the BorderStyle to Single. In your code, these are OLEObjects and an OLEObject has an Object property which in your case will be an MSForms.Textbox. So, you want to add a class module and change it's name to cTxtBox with the following code.
    <pre>Option Explicit

    Public WithEvents txtGroup As MSForms.TextBox

    Private Sub txtGroup_MouseDown(ByVal Button As Integer, _
    ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    MsgBox txtGroup.Text & " says hello!"
    End Sub</pre>


    Then in a regular code module, you create the initialization macro:
    <pre>Option Explicit

    Dim textBoxes() As New cTxtBox

    Sub initBoxes()
    Dim i As Integer
    Dim obj As OLEObject
    i = 0
    For Each obj In Worksheets("Sheet1").OLEObjects
    If TypeOf obj.Object Is MSForms.TextBox Then
    i = i + 1
    ReDim Preserve textBoxes(1 To i)
    Set textBoxes(i).txtGroup = obj.Object
    End If
    Next obj
    End Sub</pre>


    I know that you are saying, "how could 26 lines of code be so incomprehensible," so I'm attaching a sample workbook with eveything in place. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Basalt, Colorado, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine which text box(shape) called macro (97 )

    Steve,
    Thanks a lot, this opened up a whole new concept to me as far as adding new classes.

    Dan Arbaney

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Basalt, Colorado, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine which text box(shape) called macro (97 )

    Sam,

    Thanks.

    This is exactly what I was looking for!
    It would have taken me forever to figure this out myself.

    Dan Arbaney

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Determine which text box(shape) called macro (97 )

    > It would have taken me forever
    Yep, I think that I cried for about a week the first time I tried it with textboxes on a Userform. Glad that it helped. Now, it's fun to do. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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