Problem with looping through userform controls

[Jeremy Gollehon]

The following code has been working for a few years with no problems. Now
that we've installed some new computers around the office there's a strange
error happening. On the new computers, the TypeName of "MN_optOn" is read
correctly as OptionButton. However, when it goes through the TypeOf
statements it makes it into the MSForms.Checkbox block (incorrectly) and
throws an error (correctly). I've checked the references and they appear to
be the same on all computers and they all have a reference to 'Microsoft
Forms 2.0 Object Library'. This one's got me baffled. Any help is greatly
appreciated.

Thanks.
-Jeremy

---------------------------------------------------------------------------
Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control

Set colMCF_Events = New Collection
For Each ctl In Me.Controls
If ctl.Name = "MN_optOn" Then
Debug.Print TypeName(ctl)
End If
'Fill checkbox collection
If TypeOf ctl Is MSForms.CheckBox Then
Set ctlChkbox = New MCF_Events
Set ctlChkbox.Chkbox = ctl
colMCF_Events.Add ctlChkbox
End If
'Fill Option button collection
If TypeOf ctl Is MSForms.OptionButton Then
Set ctlOptButton = New MCF_Events
Set ctlOptButton.Optbtn = ctl
colMCF_Events.Add ctlOptButton
End If
'Fill combobox collection
If TypeOf ctl Is MSForms.ComboBox Then
Set ctlDropdown = New MCF_Events
Set ctlDropdown.Cbobox = ctl
colMCF_Events.Add ctlDropdown
End If
Next ctl

End Sub
---------------------------------------------------------------------------

 

[Bob Phillips]

Why do you apply all tests to every object, why not nest them

If ... Then
...
ElseIf ... Then
....
ElseIf ... Then
...
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jeremy Gollehon]

Good point Bob. I wrote this a long time ago when I was starting out. I
didn't even think to question the construct of If statement. However, my
question is still open. Do you have any ideas?

I got it working by using:
If TypeName(ctl) = "CheckBox"
etc..

However, I'm still curious to know what's breaking the code when using:
If TypeOf ctl Is MSForms.CheckBox

Thanks.

 

[Bob Phillips]

Jeremy,

Afraid I have no ideas, I stripped it down and tested it but could not
reproduce your problem.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[39N 95W]

You said there are new computers? Just a shot in the dark, but are all the
necessary References set on these new computers?

I too could not duplicate your error (in Windows XP Pro SP 2, Excel 2002)

-gk-

 

[Jeremy Gollehon]

Thanks for trying guys.

39N 95W,
From my original post:
"I've checked the references and they appear to
be the same on all computers and they all have a reference to 'Microsoft
Forms 2.0 Object Library'."

I've moved to the following, which does works on all computers.
---------------------------------------------------------------------------
Select Case TypeName(ctl)
Case "CheckBox"
Set ctlChkbox = New MCF_Events
Set ctlChkbox.Chkbox = ctl
colMCF_Events.Add ctlChkbox
Case "OptionButton"
Set ctlOptButton = New MCF_Events
Set ctlOptButton.Optbtn = ctl
colMCF_Events.Add ctlOptButton
Case "ComboBox"
Set ctlDropdown = New MCF_Events
Set ctlDropdown.Cbobox = ctl
colMCF_Events.Add ctlDropdown
End Select
---------------------------------------------------------------------------

-Jeremy

You said there are new computers? Just a shot in the dark, but are
all the necessary References set on these new computers?

I too could not duplicate your error (in Windows XP Pro SP 2, Excel
2002)

-gk-

--