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

[bllittle]

I have some code in Excel 2003 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 (when in fact the type of OLEObject it is is a ComboBox).

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

 

[bllittle]

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

 

[bllittle]

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.

--- code ---
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

--- end code ---

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.

 

[GS]

It happens that bllittle formulated :

Dim lngRecords, lngFields, lngRows, lngColumns As Long

Just so you know; In the line above the only variable that is type
declared as 'Long' is the last one. The first 3 become type 'Variant'
by default because you did not specify their type.

 

[bllittle]

that's interesting. I did not know that. Not that I doubt you, but I'm
not sure it is always accurate.

For example, if I create a variant and assign a worksheet to it, I
don't get the contextual methods and properties specific to a
worksheet if I use the variable in the code. But if I declare a series
of variables, ending by typing them As Worksheet, it would appear
early binding is taking place as the contextual methods and properties
work just fine. I wonder if declaring object type variables behaves
differently than data type variables.

I thought someone was going to call me on not declaring the
DAO.Database variable -- I didn't realize I'd been using it from a
module level declaration until I tried to further streamline some
things elsewhere in the module. so, if anyone reads this long after
the fact, dbHRawData should be declared as
Dim dbHRawData As DAO.Database

 

[GS]

bllittle wrote on 4/7/2011 :

that's interesting. I did not know that. Not that I doubt you, but I'm
not sure it is always accurate.

For example, if I create a variant and assign a worksheet to it, I
don't get the contextual methods and properties specific to a
worksheet if I use the variable in the code. But if I declare a series
of variables, ending by typing them As Worksheet, it would appear
early binding is taking place as the contextual methods and properties
work just fine. I wonder if declaring object type variables behaves
differently than data type variables.

It doesn't work that way. For example, the only variable in the
following line that 'actually' is String type is the last one.

Dim strTargetWorksheet, strTargetCmb, strRecSQL, strWorkbookName,
strWorkbookLoc, strFullLocValue As String

All the others are Variant type by default "BECAUSE THEIR TYPE WAS NOT
EXPLICITLY DEFINED". Tacking a type onto the end of a delimited list
has no effect on any members of the list preceeding the last member.

Similarly, i1 in the next line is Variant type; i2 is Integer type
"BECAUSE IT WAS EXPLICITLY DEFINED AS SUCH".

Dim i1, i2 As Integer

I thought someone was going to call me on not declaring the
DAO.Database variable -- I didn't realize I'd been using it from a
module level declaration until I tried to further streamline some
things elsewhere in the module. so, if anyone reads this long after
the fact, dbHRawData should be declared as
Dim dbHRawData As DAO.Database

I assumed this was declared with module or global scope as that would
be normal practice for an app that uses database recordsets. I
personally don't use DAO with Excel projects because I prefer ADO.
Nothing against DAO; I just do a lot of dbase stuff with closed
workbooks and text files.

 

[bllittle]

bllittle wrote on 4/7/2011 :

It doesn't work that way. For example, the only variable in the
following line that 'actually' is String type is the last one.

Dim strTargetWorksheet, strTargetCmb, strRecSQL, strWorkbookName,
strWorkbookLoc, strFullLocValue As String

I understand what you're saying and is why I specified it may be
different for object type variables in contrast to data type
variables. I didn't happen to have that many in the code I posted for
you to excerpt, but the following does work for early binding:

Dim wks1, wks2, wks3 As Worksheet

I can (and have countless times) reference wks1 or wks2 and have
contextual properties and and methods specific to the Worksheet object
assist me in coding; that wouldn't be the case if they were being
viewed as Variant because there wouldn't be any specific context to
narrow the properties and methods down to.

I'm not trying to be argumentative or anything and I appreciate your
insight on what I'd clearly been misinformed on in regards to data
type variable declarations. Just pointing out there are apparently
other situations where it does work that way.

Kind regards,
Bruce

 

[GS]

bllittle pretended :

I understand what you're saying and is why I specified it may be
different for object type variables in contrast to data type
variables. I didn't happen to have that many in the code I posted for
you to excerpt, but the following does work for early binding:

Dim wks1, wks2, wks3 As Worksheet

That's because a Variant type CAN BE ANYTHING, INCLUDING AN OBJECT!

If you didn't declare the last one they'd all still work anyway. Fact
is, the first 2 are being handled by VBA as worksheet objects while the
3rd one doesn't require VBA to evaluate it as a worksheet object
because you declared it as such. Technically, you could always just Dim
vars without ever declaring type and VBA will not object. Problem is
that it adds horrific resources overhead (Variant types require more
memory than most other types, and VBA has to evaluate it to determine
how to handle it!) to your project for VBA to have to determine the
type as code executes.

I can (and have countless times) reference wks1 or wks2 and have
contextual properties and and methods specific to the Worksheet object
assist me in coding; that wouldn't be the case if they were being
viewed as Variant because there wouldn't be any specific context to
narrow the properties and methods down to.

I'm not trying to be argumentative or anything and I appreciate your
insight on what I'd clearly been misinformed on in regards to data
type variable declarations. Just pointing out there are apparently
other situations where it does work that way.

As I said, Variant types will always work regardless of what you use

 

[keno]

bllittle wrote on 04/07/2011 17:39 ET

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

cod
Sub cbwLoadCombos(
Dim recTemp As DAO.Recordse
Dim lngRecords, lngFields, lngRows, lngColumns As Lon
Dim strTargetWorksheet, strTargetCmb, strRecSQL, strWorkbookName
strWorkbookLoc, strFullLocValue As Strin
Dim cmbTemp As ComboBo
Dim objTemp As OLEObjec
Dim i1, i2 As Intege
Dim wkbCmbAutomate As Workboo
Dim wksCmbAutomate As Workshee
ReDim strDropDownMaintenance(1 To 3, 1) As Strin
Dim rngCmbDataRange As Rang

With Applicatio
Set wkbCmbAutomate = .ActiveWorkboo
.ScreenUpdating = Fals
End Wit
'Get the information about the combobox controls embedded on eac
workshee
With wkbCmbAutomat
Set rngCmbDataRang
= .Worksheets("AutomationInfo").Range("F1"
i1 =
Do While Not (rngCmbDataRange.Cells(i1, 1).Value = ""
Set rngCmbDataRange = rngCmbDataRange.Resize(i1, 1
i1 = i1 +
Loo
i2 = rngCmbDataRange.Rows.Coun

Set rngCmbDataRange = rngCmbDataRange.Resize(i2, 3
ReDim strDropDownMaintenance(3, 1 To i2

For i1 = 2 To i
With rngCmbDataRang
For f = 1 To
strDropDownMaintenance(f, i1) = .Cells(i1
f).Valu
Next
End Wit
Next i
'Get information about where the workbook currently is and set th
datasourc
strWorkbookName = .Nam
strWorkbookLoc = .Pat
strFullLocValue = strWorkbookLoc & "" & strWorkbookNam
Set dbHRawData = OpenDatabase(strFullLocValue, False, False
"Excel 8.0") ';HDR=Yes

'Move through objects worksheet by workshee
For i1 = 2 To i
strTargetWorksheet = strDropDownMaintenance(1, i1
Do While strTargetWorksheet = strDropDownMaintenance(1
i1
With .Worksheets(strTargetWorksheet
strTargetCmb = strDropDownMaintenance(2, i1
Set objTemp = .OLEObjects(strTargetCmb
Set cmbTemp = objTemp.Objec
With dbHRawDat
strRecSQL = strDropDownMaintenance(3, i1
Set recTemp = .OpenRecordset(strRecSQL
Type:=dbOpenDynaset
End Wit
With recTem
.MoveFirs
.MoveLas
lngRecords = .RecordCoun
lngFields = .Fields.Coun
.MoveFirs
End Wit
With cmbTem
.ColumnCount = lngField
.Column = recTemp.GetRows(lngRecords
End Wit
End Wit
i1 = i1 +
If i1 > i2 The
Exit D
End I
Loo
Next i
End Wit
'clean up object variable
Set recTemp = Nothin
Set objTemp = Nothin
Set wksCmbAutomate = Nothin
Set wkbCmbAutomate = Nothin
Set dbHRawData = Nothin
Application.ScreenUpdating = Tru
End Su

end cod

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

I need some help. I have 8 combo boxes and getting an error on the row count
What would i need to change?