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

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" <j_gollehonBLAMSPAM@hotmail.com> wrote in message
news:%23KvvLlFFFHA.3376@TK2MSFTNGP12.phx.gbl...
> 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
> --------------------------------------------------------------------------
-
>
>
>
>

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" <j_gollehonBLAMSPAM@hotmail.com> wrote in message
> news:%23KvvLlFFFHA.3376@TK2MSFTNGP12.phx.gbl...
>> 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
>> -------------------------------------------------------------------------
-
>> -

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" <j_gollehonBLAMSPAM@hotmail.com> wrote in message
news:%23U2qk2FFFHA.3376@TK2MSFTNGP12.phx.gbl...
> 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" <j_gollehonBLAMSPAM@hotmail.com> wrote in message
> > news:%23KvvLlFFFHA.3376@TK2MSFTNGP12.phx.gbl...
> >> 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
>
>> -------------------------------------------------------------------------
> -
> >> -
>
>

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" <j_gollehonBLAMSPAM@hotmail.com> wrote in message
news:%23KvvLlFFFHA.3376@TK2MSFTNGP12.phx.gbl...
> 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
> ---------------------------------------------------------------------------
>
>
>
>

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" <j_gollehonBLAMSPAM@hotmail.com> wrote in message
> news:%23KvvLlFFFHA.3376@TK2MSFTNGP12.phx.gbl...
>> 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
>> -------------------------------------------------------------------------
--