Clever Geek Handbook

Excel: collateral and flanking interference?

I am making an Excel form with some activeX controls and I have a problem involving the following functionality:

enter image description here

I want users to select a number in ComboBox11. If the number is 0, the form changes so that comboboxes 9 and 10 are disabled (using VBA code) and the table below "disappears" (using conditional formatting), informing the user not to fill it out.

On the other hand, if the user selects a number greater than 0, the form remains as it is. Below the table there is a checkbox (checkbox1) used to expand the table (hiding previously hidden rows) if the data required for placement in the form is larger than the size of the table.

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:

    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 choose something greater than 0, say 1 in combobox11 and then try to expand the table by clicking checkbox1, the table expands, but I get an error:

Runtime error "1004": Unable to set properties: class included: OLEObject

(not sure about the exact text of the error, since I am not using English MSOffice)

When you click the "Debug" button, the following line in Sub ComboBox11_Change()

:

Me.ComboBox9.Enabled = True

The strange thing is that this error does not appear when combobox11 is left empty (no value selected).

I have no idea why the checkbox will interact with other comboboxes. I am confused and any help would be greatly appreciated.

+3


source to share


2 answers


Since the comments are too short to explain what I did to solve the problem, I am posting this answer.

First of all, thanks to Axel Richter, who was in no hurry to talk about my problem in detail.

Combobox11 has been populated with a generated ListFillRange in the name manager:

enter image description here



The described error stopped showing up as soon as I changed the ListFillRange. I tried a simple alternative first: "=list!AU1:AU40"

Although I don't understand the problem, it is now solved!

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

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


source


To reproduce this:

Is there such a sheet: enter image description here

A1: A6 is the ListFillRange from the ComboBox.

Then hiding any line between 1: 6 using VBA code in CheckBox1_Change () will result in an 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 called "list" that refers to the entire column, for example: enter image description here and use this "list" as a ListFillRange ComboBox, then an error occurs if the rows on this sheet were hidden from code. It does not depend on whether the hidden lines are in real active "lists" of lines 1-6 or not.

If we do not refer to the whole column in the name, but for example only lines 1-10, for example:

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


then the error only occurs if the code is hiding lines 1 through 10, but not if they are hiding lines 11 up.

Edit:

Continue with my example: Moved numbers from Sheet1! A: A to Sheet2! A: A and created a list of named named ranges referring 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's in a module of the Sheet2 class:

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

This will result in an error if the ListFillRange parameter is checked in a module of the Sheet1 class.

+2


source






More articles:


All Articles