CODEWITHSUNDEEP
excelvbacheckboxcombobox
Gašper Zupančič
Gašper Zupančič

Reputation: 81

Excel: combobox and checkbox interference?

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.

Upvotes: 3

Views: 1638

Answers (2)

Gašper Zupančič
Gašper Zupančič

Reputation: 81

Because comments are too short to explain whad I did to solve the issue, I am posting this answer.

First of all thanks to Axel Richter, who took his time to elaborate on my problem.

The Combobox11 was filled with a generated ListFillRange in the name manager:

enter image description here

The described error stopped appearing, as soon as I changed the ListFillRange. At first I tried a simple alternative: "=list!AU1:AU40" Although I don't understand the problem it is now solved!

Afterwards I used the following code to create a dynamic list for combobox11.

        Dim zadnji As Integer
        zadnji = Sheets("Form").Range("T9").Value + 1
        Me.OLEObjects("combobox11").ListFillRange = "=lists!AU1:AU" & zadnji

Upvotes: 1

Axel Richter
Axel Richter

Reputation: 61945

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.

Upvotes: 2

Related Questions