Active Object Name Syntax

[osx99]

In excel I have a combobox from the control toolbox menu named 'H1_1'

I have an event which runs from Private Sub H1_1_gotfocus()

What is the correct syntax to capture the object name?

Eg this doesn't work

Private Sub H1_1_gotfocus()

MsgBox (screen.ActiveControl.Name)

end sub

thank you
os

 

[PHV]

You can use ActiveControl in an UserForm.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[osx99]

So what do I use instead for a control toolbox object not in a userform?

 

[HarleyQuinn]

It might be better to tell us what you're trying to achieve. The reason I say this is that in the code you posted the control with focus will be H1_1 as you are using the gotfocus event, as this is the case there is no real call to dynamically find the control's name.

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[osx99]

Ok, all I want to do is avoid duplication of code which have identical events. So for instance Comboboxes H1_1 to H_20 will all do the same thing where the event code for H1_1 is

Private Sub H1_1_gotfocus()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim i As Long

Me.H1_1.List = Worksheets("Test").Range("da1:db100").Value

For i = H1_1.ListCount - 1 To 0 Step -1
   If H1_1.List(i, 0) = "" Or Len(H1_1.List(i, 1)) <> 2 Then
    With H1_1
     .RemoveItem (i)
   
     End With
   End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

I was trying to find a way to pass the active combobox name so I only have to write this once a call some generic code

Something along the lines of

Private Sub H1_1_gotfocus()
genriccode(comboxbox.name)
end sub

Private Sub H1_2_gotfocus()
genriccode(comboxbox.name)
end sub

etc...

 

[PHV]

Simply this ?
Private Sub H1_1_gotfocus()
genriccode("H1_1")
end sub

Private Sub H1_2_gotfocus()
genriccode("H1_2")
end sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

Alternatively you can pass the control (combobox) as argument:

Private Sub H1_1_gotfocus()
genriccode(Me.H1_1)
end sub

Sub genricode(obj As MSForms.ComboBox)
' Obj.List 'reference to list
' Obj.Parent ' reference to host worksheet
End Sub

combo