Type MisMatch with Set Statement

[RyanH]

I am getting a 'Type Mismatch Error' when I use the Set Statement in a
control of my Userform. I want to change the Enabled and BackColor
properties of certain Controls on the Userform when the Illumination ComboBox
equals certain values. I have an Array of all the Controls Names. Why am I
getting this error?

Private Sub cboIllumination_Change()

Set Fluorescents = Controls(Array("tbxBallasts", "cboBallasts",
"cboLamps1", _
"cboLamps2", "tbxLamps1",
"tbxLamps2", _
"cboOrientation1", "cboOrientation2"))

Set LEDs = Controls(Array("tbxTransformers", "cboSpacing", "tbxLEDs"))

Select Case cboIllumination

Case "Single Row T12 HO Fluorescent"
For Each Control In Fluorescents
Control.BackColor = vbWindowBackground
Next Control
For Each Control In frmLEDs.Controls
Control.Enabled = False
Next Control
For Each Control In LEDs
Control.BackColor = vbButtonFace
Next Control

Case "Single Row T8 HO Fluorescent"
For Each Control In Fluorescents
Control.BackColor = vbWindowBackground
Next Control
For Each Control In frmLEDs.Controls
Control.Enabled = False
Next Control
For Each Control In LEDs
Control.BackColor = vbButtonFace
Next Control

Case "LEDs"
For Each Control In LEDs
Control.BackColor = vbWindowBackground
Next Control
For Each Control In frmFluorescents.Controls
Control.Enabled = False
Next Control
For Each Control In Fluorescents
Control.BackColor = vbButtonFace
Next Control

End Select

'disables Estimate command button if no illumination, and visa versa
If cboIllumination = "No Illumination" Then
cmbEstimate.Enabled = False
Else
cmbEstimate.Enabled = True
End If

End Sub

Thanks in Advance,
Ryan

 

[Jim Thomlinson]

First off start declaring your variables and add option explicit to your
code. It will make situations like this a lot easier to deal with. Your code
is doing a lot of on the fly declarations using inappropriate names. For
example you are on the fly declaring a variant called Control which is a bad
idea since Control is a reserved word. Check out this link for info on
declaring variables...

http://www.cpearson.com/excel/variables.htm

Now on to your specific problem... Controls is a collection object that
holds all of the controls contained in a specific object. For example code
something like this will work on your userform (me gives you a reference to
the userform)...

dim ctl as control

for each ctl in me.controls
msgbox ctl.name
next ctl

Your line of code however
Controls(Array("tbxBallasts", "cboBallasts", "cboLamps1", ...
is looking in an unspecified collection of controls for an array of text
values. What you really want is to create your own collection of controls to
loop through... to that end try something like this...

dim colFlorescent as collection

set colFlorescent = new collection
with colFlorescent
..add tbxBallasts
..add cboBallasts
end with

now you can do something like
dim ctl as control

for each ctl in colFlorescent
msgbox ctl.name
next ctl

 

[RyanH]

Thanks for getting back with Jim. That link was definitely helpful. For
some reason I am getting an error "Object variable or with block variable not
set" on the line indicated below. Am I getting this error because I am
referencing the collections multiple times under the same Event?

I'm not sure if this matters or not, but colFluorescents, colLEDs, and
cboIllumination are all in different frames, does this matter?

Private Sub cboIllumination_Change()

Dim colFluorescents As Collection
Dim colLEDs As Collection
Dim ctrl As Control

Set colFluorescents = New Collection

With colFluorescents
.Add tbxBallasts
.Add cboBallasts
.Add cboLamps1
.Add cboLamps2
.Add tbxLamps1
.Add tbxLamps2
.Add cboOrientation1
.Add cboOrientation2
End With

Set LEDs = New Collection

With colLEDs
.Add tbxTransformers '<==ERROR ERROR
.Add lblTransformers
.Add cboSpacing
.Add lblSpacing
.Add tbxLEDs
.Add lblLEDs
End With

Select Case cboIllumination

Case "Single Row T12 HO Fluorescent"

'enables all fluorescent controls
For Each ctrl In colFluorescents
ctrl.Enabled = True
Next ctrl
For Each ctrl In colFluorescents
ctrl.BackColor = vbWindowBackground
Next ctrl

'diables all LED controls
For Each ctrl In colLEDs
ctrl.Enabled = False
Next ctrl
For Each ctrl In colLEDs
ctrl.BackColor = vbButtonFace
Next ctrl

Case "Single Row T8 HO Fluorescent"

'enables all fluorescent controls
For Each ctrl In colFluorescents
ctrl.Enabled = True
Next ctrl
For Each ctrl In colFluorescents
ctrl.BackColor = vbWindowBackground
Next ctrl

'diables all LED controls
For Each ctrl In colLEDs
ctrl.Enabled = False
Next ctrl
For Each ctrl In colLEDs
ctrl.BackColor = vbButtonFace
Next ctrl

Case "LEDs"

'disables all fluorescent controls
For Each ctrl In colFluorescents
ctrl.Enabled = False
Next ctrl
For Each ctrl In colFluorescents
ctrl.BackColor = vbButtonFace
Next ctrl

'enables all LED controls
For Each ctrl In colLEDs
ctrl.Enabled = True
Next ctrl
For Each ctrl In colLEDs
ctrl.BackColor = vbWindowBackground
Next ctrl

End Select

'disables Estimate command button if no illumination, and visa versa
If cboIllumination = "No Illumination" Then
cmbEstimate.Enabled = False
Else
cmbEstimate.Enabled = True
End If

End Sub

 

[RyanH]

Oops, there was a mispelling! But I am getting a different type of error.
No matter what Case I select the Next ctrl lines are highlighted and the
Error states "Object Required", why is this?

Thanks for the help so far,
Ryan

 

[RyanH]

Once agian, a mispelling! I should examine the code more carefully before
rushing to another question. Everything is running beautifully!! Thanks for
the help!!

Ryan