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

[JeffreyWeir]

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:

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

[Roger Govier]

Hi Maybe something like



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

[JeffreyWeir]

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 (http://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

[Jon Peltier]

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:


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.


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:


Dim TheTextBoxes() As New CText

and insert these procedures:


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):


Sub ProcessTextBox(txt as MSForms.TextBox)
If txt.Text = Range("TextBox1_default").Value Then txt.Text = ""
End Sub

[JeffreyWeir]

Awesome! I'm liking this forum a lot :)
Thanks Jon