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

JeffreyWeir

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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
 
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
 
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
 
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
 
You must log in or register to reply here.
Back
Top