Results 1 to 5 of 5

Thread: How do you reference the name of an activex control within the control's change event

  1. #1
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0

    How do you reference the name of an activex control within the control's change event



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there. I've got some activex textboxes in a sheet with some default text in them. Once the user clicks on a particular textbox, I want to clear the default text in that textbox so that the user can add their own text without having to first select the existing text and then delete it.

    So I'm using this:
    Code:
    Private Sub TextBox1_GotFocus()
    If Sheet1.TextBox1.Text = Range("TextBox1_default").Value Then Sheet1.TextBox1.Text = ""
    End Sub
    But because I'm doing this with a lot of text boxes, I want to have a function that does this, that automatically gets passed the name of the calling activex object.

    Can someone steer me in the right direction of how I can pass the activex name/details to the function, and how I would dim the activex name/details within that function?

    Thanks

  2. #2
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi Maybe something like

    Code:
    Sub SetDrilldown()
        Dim sCaller, obj
        Dim urow As Long, ucol As Long
        
        sCaller = Application.Caller
    
        With ActiveSheet.Shapes(sCaller).TopLeftCell
            urow = .Row
            ucol = .Column
            obj = .Parent.Name
        End With
      
      '...... Do your stuff with name or location
    
    End Sub
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  3. #3
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0
    Hi Roger. Thanks for that. While your suggestion would work fine for a forms control, It doesn't seem to work for an activex control (allthough I'm a VBA newbie, so might be missing something)

    I'm trying to trigger some code from an activex control, so I have to use run my code from the event raised by the user interacting with that activex control. I'm using _GotFocus and _Change events to run the code. But for some reason, Application.Caller or ME does not seem to return details of the activex control that raised the event. I've tried these variations

    • If I try application.caller in the _GotFocus event sub, I get "Type mismatch" error
    • If I try application.caller.name in the _GotFocus sub, I get a "Object Required" error
    • If I try With Me / MsgBox .Name / End With (with "/" denoting new lines) I get a very unhelpful "Sheet1" returned
    • Same goes for With Application.Caller / MsgBox Name / End With

    So I can't use your suggestion, I believe. I could replace all my activex controls with form controls, or I could forget about trying to 'functionize' my code, and just write seperate routines for each activex contlol. Or perhaps there's another solution.

    Thanks for trying to help

    Jeff

  4. #4
    You need to set up a class to handle all the textboxes. You don’t get as many events in the class as in the worksheet module, but you get enough.

    Insert a new class module, call it CText. After “Option Explicit” at the top, add this:

    Code:
    Public WithEvents MyText As MSForms.TextBox
    Choose MyText from the left hand dropdown atop the module, then click on Enter in the right hand dropdown. You get an Event procedure like the following. I’ve inserted the MsgBox just to show that it worked and could identify which textbox was clicked on.

    Code:
    Private Sub MyText_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
      MsgBox MyText.Name
    End Sub
    Now you need to activate the textboxes. Put this into the declarations section of a regular module:

    Code:
    Dim TheTextBoxes() As New CText
    and insert these procedures:

    Code:
    Sub ActivateActiveXTextBoxes()
      Dim sh As Shape
      Dim iTextBoxCount As Long
    
      ReDim TheTextBoxes(1 To 1)
    
      iTextBoxCount = 0
      For Each sh In ActiveSheet.Shapes
        If sh.OLEFormat.Object.OLEType = xlOLEControl Then
          iTextBoxCount = iTextBoxCount + 1
          ReDim Preserve TheTextBoxes(1 To iTextBoxCount)
          Set TheTextBoxes(iTextBoxCount).MyText = sh.OLEFormat.Object.Object
        End If
      Next
    End Sub
    
    Sub DeactivateActiveXTextBoxes()
      Dim iTexts As Long
    
      On Error Resume Next
      For iTexts = LBound(TheTextBoxes) To UBound(TheTextBoxes)
        Set TheTextBoxes(iTexts).TheText = Nothing
      Next
    End Sub
    Run the activation procedure, then click in a textbox. The message box will tell you which one it is, and you can follow up with whatever you need to do. I think I would put my procedures into the main module like this (untested):

    Code:
    Sub ProcessTextBox(txt as MSForms.TextBox)
      If txt.Text = Range("TextBox1_default").Value Then txt.Text = ""
    End Sub
    Jon Peltier
    Peltier Technical Services, Inc.
    http://peltiertech.com

  5. #5
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    356
    Articles
    0
    Awesome! I'm liking this forum a lot
    Thanks Jon

Posting Permissions

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