Type Mismatch error when trying to generically set ComboBox to a ComboBox variable

[CuseBruce]

I have some code I like to use to set an embedded combobox control's records when a client opens a workbook. The problem is it works fine when I "hard code" references, but now that I'm trying to make a "generic" routine, I'm having trouble with type mismatches.

The following code works:

    Dim cmbVintage, As ComboBox 
... other Dim statements... 
... stuff to set workbook...
       With .Worksheets("Test")
            Set cmbVintage = .cmbVintage
'...other stuff with that worksheet... 
        End With

where .cmbVintage is an embedded ComboBox control on the "Test" Worksheet.
I then go on to set a recordset object to gather the records I want to display. After that, I use the following code to put the variable in an Object array so I can use the same code to fill a number of similar controls with different recordsets:

        Select Case i2
...            Case 5
                Set objDropDownMaintenance(1, i2) = recVintage
                Set objDropDownMaintenance(2, i2) = cmbVintage

The following code then loads each of the combobox controls in the array:

        Set recTemp = objDropDownMaintenance(1, i2)
        With recTemp
            .MoveFirst
            .MoveLast
            lngRecords = .RecordCount
            lngFields = .Fields.Count
            .MoveFirst
        End With
 
        Set cmbTemp = objDropDownMaintenance(2, i2)
        With cmbTemp
            .ColumnCount = lngFields
            .Column = recTemp.GetRows(lngRecords)
        End With
    Next i2

I'd like to use a similar construct so that I can have a range on a reference worksheet that holds the target worksheets names, the names of the embedded controls on that worksheet and the recordset instructions.

I can get it to reference the controls by using the following:

                With .Worksheets(strTargetWorksheet)
                    strTargetCmb = strDropDownMaintenance(2, i1)
                    Set objDropDownMaintenance(1, i1) = .OLEObjects(strTargetCmb)
                End With

But when I then try to use the same technique:

        Set objTemp = objDropDownMaintenance(1, i1)
        Set cmbTemp = objTemp
        With cmbTemp
            .ColumnCount = lngFields
            .Column = recTemp.GetRows(lngRecords)
        End With

I get an error when setting the OLEObject variable to the ComboBox variable.

It would seem to me that the contruct is similar enough to what is happening with the Worksheets(x).comboboxname construction that works, but obviously not. Anyone have a suggestion on how to more directly reference an embedded object in order to do this? If not, any other suggestions on loading an embedded OLEObject on the fly? I've thought briefly about creating a range with the recordset results in another hidden worksheet and then setting the embedded control's ListFillRange to that, but not having used that property before I wanted to ask before going down that road.

Any insights would be appreciate.

Thank you,
Bruce

 

[CuseBruce]

I intended to mention that this is with Excel 2003, still the corporate standard.

 

[CuseBruce]

Part way there!

I didn't realize that OLEObject is not really the object embedded in the worksheet. By adding a dim statement for an OLEObject and adding a step to the set assignments, I do get to the actual ComboBox item and the code generally works. I have another bug with the recordset assignments, but that should be fixable now that I have this object model step through issue worked out. Here is an assignment that works:

    Dim cmbTemp As ComboBox  
    Dim objTemp As OLEObject
.... other objects dim'ed...
.... stuff to get worksheet names I want to deal with...
                With .Worksheets(strTargetWorksheet)
                    strTargetCmb = strDropDownMaintenance(2, i1)
                    Set objTemp = .OLEObjects(strTargetCmb)
                    Set cmbTemp = objTemp.Object
                    Set objDropDownMaintenance(1, i1) = cmbTemp
                End With

 

[CuseBruce]

Here is the final code that works if anyone is interested. This is where I have a worksheet with some maintenance information that I hide from the client called AutomationInfo. For the range being referenced in this code, I have the name of the Worksheet where the combobox is, the combobox name and DAO recordset SQL information.

Sub cbwLoadCombos()
    Dim recTemp As DAO.Recordset
    Dim lngRecords, lngFields, lngRows, lngColumns As Long
    Dim strTargetWorksheet, strTargetCmb, strRecSQL, strWorkbookName, strWorkbookLoc, strFullLocValue As String
    Dim cmbTemp As ComboBox
    Dim objTemp As OLEObject
    Dim i1, i2 As Integer
    Dim wkbCmbAutomate As Workbook
    Dim wksCmbAutomate As Worksheet
    ReDim strDropDownMaintenance(1 To 3, 1) As String
    Dim rngCmbDataRange As Range
 
    With Application
        Set wkbCmbAutomate = .ActiveWorkbook
        .ScreenUpdating = False
    End With
'Get the information about the combobox controls embedded on each worksheet
    With wkbCmbAutomate
        Set rngCmbDataRange = .Worksheets("AutomationInfo").Range("F1")
        i1 = 1
        Do While Not (rngCmbDataRange.Cells(i1, 1).Value = "")
            Set rngCmbDataRange = rngCmbDataRange.Resize(i1, 1)
            i1 = i1 + 1
        Loop
        i2 = rngCmbDataRange.Rows.Count
 
        Set rngCmbDataRange = rngCmbDataRange.Resize(i2, 3)
        ReDim strDropDownMaintenance(3, 1 To i2)
 
        For i1 = 2 To i2
            With rngCmbDataRange
                For f = 1 To 3
                    strDropDownMaintenance(f, i1) = .Cells(i1, f).Value
                Next f
            End With
        Next i1
'Get information about where the workbook currently is and set the datasource
        strWorkbookName = .Name
        strWorkbookLoc = .Path
        strFullLocValue = strWorkbookLoc & "\" & strWorkbookName
        Set dbHRawData = OpenDatabase(strFullLocValue, False, False, "Excel 8.0") ';HDR=Yes;
 
'Move through objects worksheet by worksheet
        For i1 = 2 To i2
            strTargetWorksheet = strDropDownMaintenance(1, i1)
            Do While strTargetWorksheet = strDropDownMaintenance(1, i1)
                With .Worksheets(strTargetWorksheet)
                    strTargetCmb = strDropDownMaintenance(2, i1)
                    Set objTemp = .OLEObjects(strTargetCmb)
                    Set cmbTemp = objTemp.Object
                    With dbHRawData
                       strRecSQL = strDropDownMaintenance(3, i1)
                       Set recTemp = .OpenRecordset(strRecSQL, Type:=dbOpenDynaset)
                    End With
                    With recTemp
                      .MoveFirst
                      .MoveLast
                      lngRecords = .RecordCount
                      lngFields = .Fields.Count
                      .MoveFirst
                    End With
                    With cmbTemp
                        .ColumnCount = lngFields
                        .Column = recTemp.GetRows(lngRecords)
                    End With
                End With
                i1 = i1 + 1
                If i1 > i2 Then
                    Exit Do
                End If
            Loop
        Next i1
    End With
'clean up object variables
    Set recTemp = Nothing
    Set objTemp = Nothing
    Set wksCmbAutomate = Nothing
    Set wkbCmbAutomate = Nothing
    Set dbHRawData = Nothing
    Application.ScreenUpdating = True
End Sub

The last issue I had to work out was not actually related to recordset objects. I had been setting all the objects first, then moving on to the recordset objects, then populating the list by using the .column property. I didn't realize that by moving from worksheet to worksheet, the objects I'd assigned to variables were losing scope. So, now I move through the items by moving from worksheet to worksheet, doing everything right there. I think it is probably a little less efficient (addressing the database object multiple times instead of once), but works.

 

[Glory]

The variables don't survive if you make them public?

 

[CuseBruce]

The variables don't survive if you make them public?

I was surprised that turned out to be the problem, but apparently not. Since I found a way of making it work (and works fast even with opening the db object multiple times to fill about a dozen and a half combo boxes with anywhere from 3 to 150 items), I'm not going to explore it any more.

I actually realized after I'd posted it that I have the db object dim'ed at the module level so I can use it in different procedures, but it is just a DAO.Database object so can be set within as well. But as posted, the code will probably not work since it is not explicitly there.