Run-time error when opening any workbook while another workbook with vba is open

[mikec798]

I have a workbook with some ActiveX comboboxes. When I have it open and open any other workbook I get "Run-time error '9'. Subscript out of range." When I debug it opens the change event for the combobox. I can't figure out what I have wrong that causes this error to come up when opening any other workbook. I'd be grateful for any tips.

Here's the code I currently have.

Private Sub ComboBox2_Change()     
    Dim wsDFD As Worksheet
    
    Set wsDFD = Worksheets("Dashboard Filtered Data")
         
    If wsDFD.Range("$B$2") <> Sheets("Dashboard").ComboBox2.Value Then _
        wsDFD.Range("$B$2") = Sheets("Dashboard").ComboBox2.Value
     
End Sub

 

[Jerry Sullivan]

Are you using the ListFillRange property to populate the Combobox list, or the LinkedCell property to report or set its value?

Using those properties can be problematic if those linked cells contain formulas or can be modified by sources other than the Combobox.

If you are using those, then one possiblity is:
1. Opening another workbook is triggering a change to the linked cells;
2. Which triggers the ComboBox2_Change Event
3. This line of code is not fully qualified with a workbook name,

   Set wsDFD = Worksheets("Dashboard Filtered Data")

...so instead referencing the workbook with Sheet "Dashboard Filtered Data", the code is trying to find that sheet in the ActiveWorkbook (the one just opened).
That's probably the reason for the Run-time error '9'. Subscript out of range."

You can avoid that error by referencing the ThisWorkbook object.
However in addition to fixing that; you should consider using alternatives to ListFillRange and LinkedCell.

 

[mikec798]

Thanks for your help.
I'm populating the Combobox using the ListFillRange with a Named Range and my named range is a dynamic range using the Offset function. What other options do I have to populate the list dynamically?

Also my workbook is a template and so I may create multiple workbooks from it and then have them all open at once. I'm guessing I need to go through all my code where I've declared the Worksheet as a variable and add ThisWorkbook to it so not to cause the code to run on another workbook when I run it in the active one.

Lastly I did the following,

Private Sub ComboBox2_Change()     
    Dim wsDFD As Worksheet: Set wsDFD = ThisWorkbook.Worksheets("Dashboard Filtered Data")
    Dim wsDBRD As Worksheet: Set wsDBRD = ThisWorkbook.Worksheets("Dashboard")
    
         
    If wsDFD.Range("$B$2") <> wsDBRD.ComboBox2.Value Then _
         wsDFD.Range("$B$2") = wsDBRD.ComboBox2.Value
     
End Sub

but it I get, "Compile error: Method or data member not found." When I change the wsDBRD.ComboBox2.Value back to ThisWorkbook.Worksheets("Dashboard").ComboBox2.Value it works. Is there a reason I can't do it this way or am I doing something wrong? I've done some searching and found some stuff about OLEObjects, but don't know what they are or if I need to use them, any guidance would be great.

 

[Jerry Sullivan]

Here's some code that you could use to populate your listbox.

First clear the ListFillRange Property in your ComboBox. The paste this code into a Standard Code Module and revise "MyList" to the your Dynamic Range Name.

Sub Populate_Control_List(objControl As Object, rList As Range)
    If rList Is Nothing Then
        objControl.Clear
    Else
        If rList.Rows.Count > 1 Then
            objControl.List = rList.Value
        Else
            objControl.Clear
            objControl.AddItem rList.Value
        End If
    End If
End Sub

Here are examples of how to call the Sub.

Call it from a macro that you could link to a button...

Sub Update_Combo2(ByVal Target As Range)   
    Call Populate_Control_List( _
        objControl:=Sheets("Dashboard").ComboBox2.Object, _
        rList:=Range("MyList"))
End Sub

Paste this into the Sheet Code module of the sheet that has the dynamic range cells.
When the list is changed, the combobox will update.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("MyList")) Is Nothing Then Exit Sub
    
    Call Populate_Control_List( _
        objControl:=Sheets("Dashboard").ComboBox2.Object, _
        rList:=Range("MyList"))
End Sub

Paste this into the ThisWorkbook Code module
This will populate the list each time the workbook is opened.

Private Sub Workbook_Open()
    Call Populate_Control_List( _
        objControl:=Sheets("Dashboard").ComboBox2.Object, _
        rList:=Range("MyList"))
End Sub

 

[Jerry Sullivan]

Lastly I did the following,

Private Sub ComboBox2_Change()     
    Dim wsDFD As Worksheet: Set wsDFD = ThisWorkbook.Worksheets("Dashboard Filtered Data")
    Dim wsDBRD As Worksheet: Set wsDBRD = ThisWorkbook.Worksheets("Dashboard")
    
         
    If wsDFD.Range("$B$2") <> wsDBRD.ComboBox2.Value Then _
         wsDFD.Range("$B$2") = wsDBRD.ComboBox2.Value
     
End Sub

but it I get, "Compile error: Method or data member not found." When I change the wsDBRD.ComboBox2.Value back to ThisWorkbook.Worksheets("Dashboard").ComboBox2.Value it works. Is there a reason I can't do it this way or am I doing something wrong? I've done some searching and found some stuff about OLEObjects, but don't know what they are or if I need to use them, any guidance would be great.

The simplest way to reference ComboBox2 from this procedure would be to use the Me Keyword...

Private Sub ComboBox2_Change()
    Dim wsDFD As Worksheet: Set wsDFD = ThisWorkbook.Worksheets("Dashboard Filtered Data")
    Dim wsDBRD As Worksheet: Set wsDBRD = ThisWorkbook.Worksheets("Dashboard")
         
    If wsDFD.Range("$B$2") <> Me.ComboBox2.Value Then _
         wsDFD.Range("$B$2") = Me.ComboBox2.Value
     
End Sub

The example you gave it is really interesting though. I have to confess I don't understand why that doesn't work.

The problem seems to be when you try to use a Variable that represents a Worksheet object, VBA can't seem to find the ComboBox2 as a member of that object. Seems to be a container issue.

I'd be interested if someone can explain why these three statements cause compile errors, but the others don't.

Sub Why()
    Dim wsDBRD As Worksheet
    Dim wb As ThisWorkbook
    Dim ws As Worksheet, sh As Worksheet
    Set wb = ThisWorkbook
    Set ws = Worksheets("Dashboard")
    Set wsDBRD = wb.Worksheets("Dashboard")
    Set sh = Sheets("Dashboard")

 
[COLOR="#008080"] '--these work as expected  [/COLOR]
    Debug.Print Worksheets("Dashboard").ComboBox2.Value 'OK
    Debug.Print ThisWorkbook.Worksheets("Dashboard").ComboBox2.Value  'OK
    Debug.Print wb.Worksheets("Dashboard").ComboBox2.Value  'OK

[COLOR="#008080"]'--the next 3 statements will each generate:
'-- Compile error Method or data member not found (highlights ComboBox2)[/COLOR]
    Debug.Print ws.ComboBox2.Value  'Compile error
    Debug.Print wsDBRD.ComboBox2.Value  'Compile error
    Debug.Print sh.ComboBox2.Value  'Compile error

  
[COLOR="#008080"]'Using OLEObjects.Object seems to allow the use of these variables[/COLOR]
    Debug.Print Worksheets("Dashboard").OLEObjects("ComboBox2").Object.Value 'OK
    Debug.Print ws.OLEObjects("ComboBox2").Object.Value 'OK
    Debug.Print ThisWorkbook.Worksheets("Dashboard") _
        .OLEObjects("ComboBox2").Object.Value 'OK
    Debug.Print wb.Worksheets("Dashboard").OLEObjects("ComboBox2").Object.Value 'OK
    Debug.Print wsDBRD.OLEObjects("ComboBox2").Object.Value 'OK
    Debug.Print sh.OLEObjects("ComboBox2").Object.Value 'OK
     
End Sub