ComboBox Exit

[Guest]

Hio All,

I have a few ComboBoxes on a userform e.g. C1, C2 C3 etc.
I want to put the same procedure for all of them which
would be trigered on exitiing the relevant ComboBox. The
codes would include the following line, say exiting C1:

C2.enabled = false
C3.enabled = false

My two questions are:

1. Can I write a single (I ahve quite a few Combos)
procedure that will perform on exiting any combo on the
userform and how?
2. Can the above code be included?

Thanks for your help in advance.

Regards

 

[Tom Ogilvy]

Assuming the Comboboxes are on a userform.

For methods that belong to the combobox, you can. However, the exit event
belongs to the control object and for that you can't. You can write single
routine which is called from the exit event of each combobox, so that would
reduce you burden somewhat.

Private Sub C1_Exit()
ExitCode C1
End Sub

Sub ExitCode(cbx as MSForms.Combobox)
dim cbx1 as MSForms.Combobox
dim ctl as Control
for each ctl in Userform1.Controls
if typeof ctl is MSForms.Combobox then
set cbx1 = ctl
if cbx1.Name <> cbx.Name then
cbx1.Enabled = False
end if
end if
Next
End Sub

 

[Bob Phillips]

Here's a technique I got from John Walkenbach, and have adapted for your
combos.

First, create a class with the following code

Public WithEvents ComboGroup As ComboBox

Private Sub ComboGroup_Click()
MsgBox "Hello from " & ComboGroup.Name
End Sub


The click event here is your universal procesure, put your specific code in
their.

Then in a normal module, add this procedure to setup the combobox array.

Dim Combos() As New Class1

Sub ShowDialog()
Dim cCombos As Long
Dim ctl As Control

' Create the Combobox objects
cCombos = 0
For Each ctl In UserForm1.Controls
If TypeName(ctl) = "ComboBox" Then
cCombos = cCombos + 1
ReDim Preserve Combos(1 To cCombos)
Set Combos(cCombos).ComboGroup = ctl
End If
Next ctl
UserForm1.Show
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob,

Thanks for this interesting codes. I will try it. Thanks
for your help.

Regards
Sadik

 

[Guest]

Hi Tom,

Didn't think of this method. This makes sense. Thanks for
your help.

Regards
Sadik