R
rvExcelTip
I'm stuck with a dreaded Error 13: Type Mismatch when referencing a
Excel control in a subroutine.
Here's the background: The Workbook contains a User Defined for
-frmGraphSelection- that holds a Listbox called -lstGroup-. The goa
is to fill the list with data from a particular column in the workshee
-"History"- also contained in that workbook.
Following code was used (purged from statements not relevant to th
problem). All code is at the ThisWorkBook level.
-Private Sub Workbook_Open()
'calculate last row number
Dim lngLastRow As Long
lngLastRow
rvxlGetLastCellCoordinate(ThisWorkbook.Worksheets(c_strHistory))
Dim strGroupRange As String
strGroupRange = c_strGroupColumn & CStr(c_lngRowStart + 1) & ":"
c_strGroupColumn & CStr(lngLastRow)
Dim rngGroup As Range
Set rngGroup
ThisWorkbook.Worksheets(c_strHistory).Range(strGroupRange)
Call ColumnToList(frmGraphSelection.lstGroup, rngGroup)
Set rngGroup = Nothing
End Sub-
-Private Sub ColumnToList(lstTarget As ListBox, rngSource As Range)
'* Purpose: Transfers non-empty cells in the column range to a listbox
'* Accepts: -lstTarget: the listbox to be filled
'* -rngSource: the range (-single column) from which th
cell values
'* are to be transferred. The range should star
beyond the column
'* headers!! (otherwise these too will be transferred
'Range is transferred into a variant for speedy processing
' cfr excel 2002 vba, wrox, pp 122-123
Dim a_varRange As Variant
a_varRange = rngSource.Value
Dim lngListIndex As Long
lngListIndex = 0 'Zero Based
Dim lngIndex As Long 'Always 1-based !!
For lngIndex = 1 To UBound(a_varRange, 1)
Select Case Not IsEmpty(a_varRange(lngIndex, 1))
Case True
With lstTarget
.List(lngListIndex, 0) = c_lngRijValiditeit + lngIndex
.List(lngListIndex, 1) = a_varRange(lngIndex, 1)
End With
lngListIndex = lngListIndex + 1
Case False
End Select
Next lngIndex
End Sub-
The offending message shows up at But when I check wit
-?TypeName(frmGraphSelection.lstGroup)- in the immediate window, i
does answer with ... ListBox!!
When I change the Module definition to -
Private Sub ColumnToList(lstTarget As Object, rngSource As Range)- the
the message disappears, but within the module the methods e.g. Wit
lstTarget .List( ... are no longer recognized.
The c_ variables are public constants, defined elsewhere. Th
ColumnToList routine may contain errors, as I couldn't even get int
testing it
Excel control in a subroutine.
Here's the background: The Workbook contains a User Defined for
-frmGraphSelection- that holds a Listbox called -lstGroup-. The goa
is to fill the list with data from a particular column in the workshee
-"History"- also contained in that workbook.
Following code was used (purged from statements not relevant to th
problem). All code is at the ThisWorkBook level.
-Private Sub Workbook_Open()
'calculate last row number
Dim lngLastRow As Long
lngLastRow
rvxlGetLastCellCoordinate(ThisWorkbook.Worksheets(c_strHistory))
Dim strGroupRange As String
strGroupRange = c_strGroupColumn & CStr(c_lngRowStart + 1) & ":"
c_strGroupColumn & CStr(lngLastRow)
Dim rngGroup As Range
Set rngGroup
ThisWorkbook.Worksheets(c_strHistory).Range(strGroupRange)
Call ColumnToList(frmGraphSelection.lstGroup, rngGroup)
Set rngGroup = Nothing
End Sub-
-Private Sub ColumnToList(lstTarget As ListBox, rngSource As Range)
'* Purpose: Transfers non-empty cells in the column range to a listbox
'* Accepts: -lstTarget: the listbox to be filled
'* -rngSource: the range (-single column) from which th
cell values
'* are to be transferred. The range should star
beyond the column
'* headers!! (otherwise these too will be transferred
'Range is transferred into a variant for speedy processing
' cfr excel 2002 vba, wrox, pp 122-123
Dim a_varRange As Variant
a_varRange = rngSource.Value
Dim lngListIndex As Long
lngListIndex = 0 'Zero Based
Dim lngIndex As Long 'Always 1-based !!
For lngIndex = 1 To UBound(a_varRange, 1)
Select Case Not IsEmpty(a_varRange(lngIndex, 1))
Case True
With lstTarget
.List(lngListIndex, 0) = c_lngRijValiditeit + lngIndex
.List(lngListIndex, 1) = a_varRange(lngIndex, 1)
End With
lngListIndex = lngListIndex + 1
Case False
End Select
Next lngIndex
End Sub-
The offending message shows up at But when I check wit
-?TypeName(frmGraphSelection.lstGroup)- in the immediate window, i
does answer with ... ListBox!!
When I change the Module definition to -
Private Sub ColumnToList(lstTarget As Object, rngSource As Range)- the
the message disappears, but within the module the methods e.g. Wit
lstTarget .List( ... are no longer recognized.
The c_ variables are public constants, defined elsewhere. Th
ColumnToList routine may contain errors, as I couldn't even get int
testing it