Hi All,

Here is my problem which hopefully somebody can assist with:

I have a userform (Critique) which has a number of combo boxes added to it
dynamically because the amoun of these boxes can vary depending on other
cicumstances.

I now have a need to classify each combobox into one of two categories - the
way I do this is through using the tag property to differentiate.

Depending on the tag proprerty (say it is either 1 or 2) for the currently
in use Combobox, I need it to then make a textbox visible and another
invisible - and vice versa.

In other words, I have 2 textboxes to grab input but which textbox is
visible depends on the tag of the combobox that is currently active.

I tried to do a class module for the "Enter" event but it doesn't work. The
"Change" event works but this will not be good enough for me as it will
require information to change before it runs whereas I want the class module
to run the moment somebody tabs into the combobox or clicks into it.

If somebody can please help it would be appreciated and I extend my thankyou
in advance.

Regards,
Clint

Hello Clinton,

I tried to do a class module for the "Enter" event but it doesn't work.

I take it you meant the Enter event is n/a in a WithEvents class for a
ComboBox. Unfortunately that's the case, a few other simlar events are
missing; not only for ComboBoxes but also missing for some other withevents
controls.

Of course these events are avalable in the Userform but if you have a lot of
controls and want to use WithEvents you have to workaround, in effect to
infer your own "Enter" or GotFocus event. Try the following, include all
but no more than the controls as listed in the userform, togther with the
two class modules as named.

''''' in a form named UserForm1, with
' 1 CommandButton
' 2 ComboBox's
' 2 TextBox's

Option Explicit
Private arrCB(1 To 2) As New clsCombos
Private arrTB(1 To 2) As New clsTBoxes

Private Sub CommandButton1_Enter()
UpdateCBfocus 0, False
End Sub

Private Sub UserForm_Initialize()
Dim c As Long, t As Long
Dim ctr As Object

CommandButton1.SetFocus ' ensure focus is not on a combobox

ComboBox1.List = Array("A", "B")
ComboBox2.List = Array("C", "D")

For Each ctr In Me.Controls
Select Case TypeName(ctr)
Case "ComboBox"
c = c + 1
Set arrCB(c).CB = ctr
arrCB(c).gIdx = c
Case "TextBox"
t = t + 1
Set arrTB(t).TB = ctr
End Select
Next

End Sub

Public Sub UpdateCBfocus(idx As Long, bFocus As Boolean)
Dim i As Long, s As String

For i = 1 To UBound(arrCB)
arrCB(i).gbGotFocus = False
Next
If bFocus Then arrCB(idx).gbGotFocus = bFocus

If bFocus Then
s = arrCB(idx).CB.Name & "has focus"
Else
s = "no combo has focus"
End If
Me.Caption = s
End Sub

''''''''''''' end UserForm1 code

''''''class module named clsCombos

Option Explicit
Public WithEvents CB As MSForms.ComboBox
Public gbGotFocus As Boolean
Public gIdx As Long

Private Sub CB_Change()
'UpdateFocus True 'don't think this is helpful
End Sub

Private Sub CB_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
UpdateFocus True
End Sub

Private Sub CB_MouseUp(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
UpdateFocus True
End Sub

Private Sub UpdateFocus(b As Boolean)
Dim bEnter As Boolean

bEnter = Not gbGotFocus And b 'has just received focus

Call UserForm1.UpdateCBfocus(gIdx, b)

If bEnter Then
UserForm1.Caption = CB.Name & " just got focus"
' do update stuff here

End If

End Sub
''''''''' end clsCombos

''''''' class module named clsTBoxes

Option Explicit
Public WithEvents TB As MSForms.TextBox
Public gIdx As Long

Private Sub TB_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
UserForm1.UpdateCBfocus 0, False
End Sub

Private Sub TB_MouseUp(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
UserForm1.UpdateCBfocus 0, False
End Sub

'''''''''''''''' end clsTBoxes

Regards,
Peter T




"Clinton M James" wrote in message
...
Hi All,

Here is my problem which hopefully somebody can assist with:

I have a userform (Critique) which has a number of combo boxes added to it
dynamically because the amoun of these boxes can vary depending on other
cicumstances.

I now have a need to classify each combobox into one of two categories -
the way I do this is through using the tag property to differentiate.

Depending on the tag proprerty (say it is either 1 or 2) for the currently
in use Combobox, I need it to then make a textbox visible and another
invisible - and vice versa.

In other words, I have 2 textboxes to grab input but which textbox is
visible depends on the tag of the combobox that is currently active.

I tried to do a class module for the "Enter" event but it doesn't work.
The "Change" event works but this will not be good enough for me as it
will require information to change before it runs whereas I want the class
module to run the moment somebody tabs into the combobox or clicks into
it.

If somebody can please help it would be appreciated and I extend my
thankyou in advance.

Regards,
Clint

How about use the KeyDown and the MouseDown events of your class module?

RBS


"Clinton M James" wrote in message
...
Hi All,

Here is my problem which hopefully somebody can assist with:

I have a userform (Critique) which has a number of combo boxes added to it
dynamically because the amoun of these boxes can vary depending on other
cicumstances.

I now have a need to classify each combobox into one of two categories -
the way I do this is through using the tag property to differentiate.

Depending on the tag proprerty (say it is either 1 or 2) for the currently
in use Combobox, I need it to then make a textbox visible and another
invisible - and vice versa.

In other words, I have 2 textboxes to grab input but which textbox is
visible depends on the tag of the combobox that is currently active.

I tried to do a class module for the "Enter" event but it doesn't work.
The "Change" event works but this will not be good enough for me as it
will require information to change before it runs whereas I want the class
module to run the moment somebody tabs into the combobox or clicks into
it.

If somebody can please help it would be appreciated and I extend my
thankyou in advance.

Regards,
Clint