Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 --------------------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -------------------------------------------------------------------------- - |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ------------------------------------------------------------------------- - - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ------------------------------------------------------------------------- - - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 --------------------------------------------------------------------------- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ------------------------------------------------------------------------- -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looping through userform controls | Excel Programming | |||
looping through userform controls changing enabled and locked properties | Excel Programming | |||
Looping through multiple controls | Excel Programming | |||
looping through all checkbox's in a userform | Excel Programming | |||
Looping thru custom controls | Excel Programming |