Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch error when trying to generically set ComboBox to aComboBox variable
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch error when trying to generically set ComboBox to aComboBox variable
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch error when trying to generically set ComboBox to aComboBox variable
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch error when trying to generically set ComboBox to a ComboBox variable
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch error when trying to generically set ComboBox to aComboBox variable
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 On Apr 7, 3:53*pm, GS wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch error when trying to generically set ComboBox to a ComboBox variable
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch error when trying to generically set ComboBox to aComboBox variable
On Apr 7, 7:55*pm, GS wrote:
bllittle wrote on 4/7/2011 : 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 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch error when trying to generically set ComboBox to a ComboBox variable
bllittle pretended :
On Apr 7, 7:55*pm, GS wrote: bllittle wrote on 4/7/2011 : 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 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 them for.<g Kind regards, Bruce -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch error when trying to generically set ComboBox to a ComboBox variable
bllittle wrote on 04/07/2011 17:39 ET :
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. I need some help. I have 8 combo boxes and getting an error on the row count. What would i need to change? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
runtime error 13 - type mismatch error in Excel 97 on Citrix | Excel Programming | |||
Excel 2003 - Combobox.AddItem type mismatch | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming |