Problem with looping through userform controls
 

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
---------------------------------------------------------------------------

Problem with looping through userform controls
 

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" wrote in message
...
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
--------------------------------------------------------------------------
-

Problem with looping through userform controls
 

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 wrote:
Why do you apply all tests to every object, why not nest them

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


"Jeremy Gollehon" wrote in message
...
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
-------------------------------------------------------------------------
-
-

Problem with looping through userform controls
 

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)


"Jeremy Gollehon" wrote in message
...
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 wrote:
Why do you apply all tests to every object, why not nest them

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


"Jeremy Gollehon" wrote in message
...
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

-------------------------------------------------------------------------
-
-

Problem with looping through userform controls
 

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" wrote in message
...
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
---------------------------------------------------------------------------

Problem with looping through userform controls
 

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


39N 95W wrote:
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" wrote in message
...
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
-------------------------------------------------------------------------
--