View Full Version : How do you reference the name of an activex control within the control's change event
2011-03-23, 06:38 PM
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 = ""
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?
2011-03-24, 12:19 AM
Hi Maybe something like
Dim sCaller, obj
Dim urow As Long, ucol As Long
sCaller = Application.Caller
urow = .Row
ucol = .Column
obj = .Parent.Name
'...... Do your stuff with name or location
2011-03-24, 01:54 AM
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
2011-03-24, 03:00 AM
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)
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:
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
Dim iTexts As Long
On Error Resume Next
For iTexts = LBound(TheTextBoxes) To UBound(TheTextBoxes)
Set TheTextBoxes(iTexts).TheText = Nothing
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 = ""
2011-03-24, 03:18 AM
Awesome! I'm liking this forum a lot :)
Powered by vBulletin® Version 4.2.0 Copyright © 2013 vBulletin Solutions, Inc. All rights reserved.