Excel: combobox and checkbox interference?

Question

I am making an Excel form with some activeX controls and am having a problem incorporating the following functionallity:

enter image description here

I wish for the users to select a number in ComboBox11. If the number be 0, the form changes so, that comboboxes 9 and 10 become disabled (using VBA code) and the table below 'fades out' (using conditional formatting), informing the user not to fill it out.

On the other hand if the user selects a number larger than 0, the form stays as it is. Under the table is a checkbox (checkbox1) used to expand the table (unhiding previously hidden rows) if data needeed to be put in the form is larger than the table size.

The VBA code behind combobox 11 is:

    Private Sub ComboBox11_change()
Dim ws As Worksheet
Set ws = Sheets("Form")
If Not Me.ComboBox11.Text = "" Then ws.Range("J24") = CInt(Me.ComboBox11.Text) 'to write integer instead of text into linked cell
    If Me.ComboBox11.Value = 0 Then
        Me.ComboBox9.Enabled = False
        Me.ComboBox10.Enabled = False
        If Me.CheckBox1.Value = True Then Me.CheckBox1.Value = False 'if combobox11 is 0 than the table doesn't need to be expanded
        Me.CheckBox1.Enabled = False
    Else
        Me.ComboBox9.Enabled = True
        Me.ComboBox10.Enabled = True
        Me.CheckBox1.Enabled = True
    End If
End Sub

And the code behind CheckBox1 is:

    Private Sub CheckBox1_Change()
Dim ws As Worksheet
Set ws = Sheets("Form")
    If CheckBox1.Value = False Then ws.Rows("46:71").Hidden = True
    If CheckBox1.Value = True Then ws.Rows("46:71").Hidden = False
End Sub

So, if I select 0 in combobox11 the result is:

enter image description here

So far so good. But if I select something larger than 0, say 1 in combobox11 and then try to expand the table by clicking on the checkbox1, the table expands, but I get an error message:

Run-time error '1004': Not possible to set properties: enabled class: OLEObject

(not sure about the exact error text, since I'm not using English MSOffice)

When pressing the Debug button, the following line in Sub ComboBox11_Change() lights up:

Me.ComboBox9.Enabled = True

The Strange thing is, that this error does not appear when the combobox11 is left blank ( a value is not selected).

I have no idea why the checkbox would interact with the other comboboxes. I am bewildered and any help would be much appreciated.

Have you tryied to use Application.EnableEvents = False at the very beginning of 'CheckBox1_Change' and Application.EnableEvents = True at the and of it? — Daniel Dušek
Which is the ListFillRange of ComboBox11 or how is the List of ComboBox11 defined? Is it possible that hiding the rows in CheckBox1_Change has effects to the List of ComboBox11? If so, why? — Axel Richter
@dee: thanks for your suggestion. Just tried that and the same error appears — Gašper Zupančič
@AxelRichter: The list field of combobox11 is an automatically generated list of integers from 0 to a value that the user selects from another combobox (not greater than 40 btw). After the top number is selected, the list is generated in a column of the same sheet (the sheet with the fill-out form). This column is then used to create a list in Excel Name Manager, which feeds the combobox. The checkbox code does not hide cells that generate that list. And even if they would, how could that be a problem? — Gašper Zupančič
Hm, seems as if you already pretty sure that this never can be the problem. My answer shows that it could be. Have you tried to reproduce my answer? So if you need help further, you should show more in detail how you generate the List for your ComboBox11. — Axel Richter

Axel Richter Axel Richter · Accepted Answer · 2014-11-25T14:17:37

To reproduce this:

Have a sheet like this: enter image description here

A1:A6 is the ListFillRange of the ComboBox.

Then hiding any row between 1:6 using VBA code in CheckBox1_Change() will throw the error.

Private Sub CheckBox1_Change()
    If CheckBox1.Value = False Then Me.Rows("7:13").Hidden = True
    If CheckBox1.Value = True Then Me.Rows("7:13").Hidden = False
    'If CheckBox1.Value = False Then Me.Rows("6:13").Hidden = True 'ERROR
    'If CheckBox1.Value = True Then Me.Rows("6:13").Hidden = False 'ERROR
End Sub

Private Sub ComboBox1_Change()
    If Me.ComboBox1.Value = 0 Then
        If Me.CheckBox1.Value = True Then Me.CheckBox1.Value = False
        Me.CheckBox1.Enabled = False
    Else
        Me.CheckBox1.Enabled = True
    End If
End Sub

If we create a name named "list" which is related to a whole column like this: enter image description here and use this "list" as the ListFillRange of the ComboBox, then the error occurs ever if rows in this sheet were hidden from code. This is independent on whether the hidden rows are within the real active "list" rows 1-6 or not.

If we not reference a whole column in the name but for example only rows 1-10 like:

=INDEX(Sheet1!$A$1:$A$10;1):INDEX(Sheet1!$A$1:$A$10;6)

then the error only occurs if the code hides rows from 1 to 10 but not if it hides rows from 11 upwards.

Edit:

To continue my example: Moved the numbers from Sheet1!A:A to Sheet2!A:A and created a named range "list" related to

=Sheet2!$A$1:INDEX(Sheet2!$A$1:$A$40;COUNTIF(Sheet2!$A$1:$A$40;"<>"))

Then the following code to set Sheet1.ComboBox1.ListFillRange to "list" will work if it is within the Sheet2 class module:

Private Sub Worksheet_Change(ByVal Target As Range)
 ThisWorkbook.Worksheets(1).ComboBox1.ListFillRange = "list"
End Sub

It will produce the error if setting the ListFillRange is tried from within the Sheet1 class module.

Excel: combobox and checkbox interference?

2 Answers