[RESOLVED] ComboBoxes - How to disable dynamically

[Mafe]

Hi,

I'm working with Excel 2003 VBA. I have a UserForm with 10 ComboBoxes.
And I have an general input. The ComBoboxes are filled dynamically , they have more or less items depending on this input.
When this input is less than a defined value, I disable e.g. ComboBox10. and so on. Below is part of the code (how I fill and how I disable).
I would like to know an efficient way to write a less code as possible when I need to disable several ComboBoxes.
Code used to fill part of them using an array:

With ComboBox2
                  For index = 1 To Num
                           .AddItem RackID(index)
                  Next index
         End With
       With ComboBox3
                  For index = 1 To Num
                           .AddItem RackID(index)
                  Next index
         End With

etc etc....
Code used to disable them:

If Num = 5 Then 'MyCell8 = 40000
                  With ComboBox6
                           .Enabled = False
                           .BackColor = &H8000000F
                  End With
                  With ComboBox7
                           .Enabled = False
                           .BackColor = &H8000000F
                  End With
                  With ComboBox8
                           .Enabled = False
                           .BackColor = &H8000000F
                  End With
                  With ComboBox9
                           .Enabled = False
                           .BackColor = &H8000000F
                  End With
                  With ComboBox10
                           .Enabled = False
                           .BackColor = &H8000000F
                  End With
         End If

I can say that Im a newbie. Thanks for your help,
Mafe

[Hack]

Moved to Office Development

[Al42]

Make the comboboxes an array, then you can loop to disable them

If Num = 5 Then 'MyCell8 = 40000
  For i = 6 to 10
    ComboBox(i).Enabled = False
    ComboBox(i).BackColor = &H8000000F
  Next i
End If

[Mafe]

Hi,
Thanks for your help. I have just tried this Combobox array and defined it when initializing the userform this way:

Dim ComboBox (6 to 10) As ComboBox

And then applied your code for that specific ComboBoxes. But I get the following error:

Run time error '91'
Object variable or With block variable not set

I tried to use the Set statement as follows:

For i= 6 to 10
   Set ComboBox(i).Enabled = False
Next i

But then I get
Compile error: Invalid use of property.

Have I definied the wrong way the array? Mafe

[Siddharth Rout]

Hi

This is the way you can create control array at runtime.

and then you can the use Al42's code in post#3 to disable them...

Hope this helps...

[Mafe]

Hi again,

Thanks for the link. I have read it thouroughly and tried the second method. And I have defined my Object variable as follows:

Dim RackID() As String
Dim CboName(1 To 10) As Control
 
D = vbKeyA
         For index = 1 To Num
                  RackID(index) = MyChar & MyChar1
                  Debug.Print index, RackID(index)
                  ID = ID + 1
                  MyChar1 = String(1, ID)
         Next index
         Set CboName(1) = UserForm1.Controls.Add("MSForms.ComboBox", "ComboBox1", UserForm1)
         Set CboName(2) = UserForm1.Controls.Add("MSForms.ComboBox", "ComboBox2", UserForm1)
         Set CboName(3) = UserForm1.Controls.Add("MSForms.ComboBox", "ComboBox3", UserForm1)
         Set CboName(4) = UserForm1.Controls.Add("MSForms.ComboBox", "ComboBox4", UserForm1)
         Set CboName(5) = UserForm1.Controls.Add("MSForms.ComboBox", "ComboBox5", UserForm1)
         Set CboName(6) = UserForm1.Controls.Add("MSForms.ComboBox", "ComboBox6", UserForm1)
         Set CboName(7) = UserForm1.Controls.Add("MSForms.ComboBox", "ComboBox7", UserForm1)
         Set CboName(8) = UserForm1.Controls.Add("MSForms.ComboBox", "ComboBox8", UserForm1)
         Set CboName(9) = UserForm1.Controls.Add("MSForms.ComboBox", "ComboBox9", UserForm1)
         Set CboName(10) = UserForm1.Controls.Add("MSForms.ComboBox", "ComboBox10", UserForm1)
 
    For index = 1 To 10
                  With CboName(index)
                           For index1 = 1 To Num
                                    .AddItem RackID(index)
                           Next index1
                  End With
         Next index
         CboName(1).Font.Size = 8
         CboName(1).ListIndex = 0

According to the link, it should be VB instead of MSForms, see below.

Set CboName(10) = UserForm1.Controls.Add("[B]VB[/B].ComboBox", "ComboBox10", UserForm1)

But, when i look the object browser this is the only library where I find ComboBox or Controls.
And I get an error:

Run time error:'-2147221005 (800401f3)':
Invalid class string

I try to load with string items that are within RackID() array.

Thanks for your help !! Mafe

[Mafe]

Just the way I wrote the solution for those that are still interested. For Excel 2003.
The important was how to write the correct Set statement.

Dim CboName(1 to 10) As ComboBox
 
Set CboName(1) = UserForm1.ComboBox1
Set CboName(2) = UserForm1.ComboBox2
Set CboName(3) = UserForm1.ComboBox3
Set CboName(4) = UserForm1.ComboBox4
Set CboName(5) = UserForm1.ComboBox5
Set CboName(6) = UserForm1.ComboBox6
Set CboName(7) = UserForm1.ComboBox7
Set CboName(8) = UserForm1.ComboBox8
Set CboName(9) = UserForm1.ComboBox9
Set CboName(10) = UserForm1.ComboBox10
'Add items to ComboBoxes
For index = 1 To 10
                  With CboName(index)
                           For index1 = 1 To Num
                                    .AddItem RackID(index1)
                           Next index1
                  End With
 Next index
If Num = 5 Then 'MyCell8 = 40000
For index = 6 To 10
                  With CboName(index)
                        .Enabled = False
                        .BackColor = &H8000000F
                  End With
 Next index

Thanks for all help!! Sorry for late replies!! Mafe