I have a worksheet with a dozen or so activex comboboxes on.

How can I tell in vba which one is currently active? (i.e. which one has the cursor in?)

Thanks.

"Khuli" wrote in message

I have a worksheet with a dozen or so activex comboboxes on.

How can I tell in vba which one is currently active? (i.e. which one has
the cursor in?)


I don't think there is a direct way, like say returning ActiveControl on a
form.

Maybe you could adapt something like the following though it'd mean trapping
focus for all your controls, error handling, and what happens when the sheet
is activated or deactivated which I didn't look at

, in the worksheet module
Private msActiveAX As String

Private Sub ComboBox1_GotFocus()
msActiveAX = "ComboBox1"
End Sub
Private Sub ComboBox1_LostFocus()
msActiveAX = ""
End Sub

Sub test()
Dim ole As OLEObject
Dim oleCbo As MSForms.ComboBox
Debug.Print msActiveAX
If Len(msActiveAX) Then
Set ole = Me.OLEObjects(msActiveAX)
If InStr(1, ole.progID, "ComboBox") Then
Set oleCbo = ole.Object
End If
End If
End Sub

Unfortunately Got & LostFocus are not exposed with WithEvents

Peter T

Hi Peter

Thanks for the suggestion - I figured there wasn't a 'real' way to do it :(

What about creating a global variable, and in the GotFocus/LostFocus events I could save/clear the name of the current control. Is that reasonable?

"Khuli" wrote in message
Hi Peter

Thanks for the suggestion - I figured there wasn't a 'real' way to do it
:(

What about creating a global variable, and in the GotFocus/LostFocus
events I could save/clear the name of the current control. Is that
reasonable?

That's virtually what the example does, adds or clears the name at module
level in the sheet "class" module. If you prefer it fully global declare
the variable in a normal module, say

Public gsActiveAX As String

FWIW you could save/destroy an object reference, but for something like this
I'd prefer to store the name and recreate the reference with the name.

Peter T

Hi Peter

Thanks for the suggestion - I figured there wasn't a 'real' way to do
it :(

What about creating a global variable, and in the GotFocus/LostFocus
events I could save/clear the name of the current control. Is that
reasonable?

Optionally...

Public gcboActive As Object '//in a standard module

Then in the sheet code module:
Private Sub ComboBox1_GotFocus()
Set gcboActive = Me.ComboBox1
End Sub
Private Sub ComboBox1_LostFocus()
Set gcboActive = Nothing
End Sub

Then ref it in code like this...

Sub DoThis()
If gcboActive = Nothing Then Exit Sub
If gcboActive.Text = <string Then...
OR
If gcboActive.ListIndex = <number Then...
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion