Referencing an Excel Control as an argument

[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

 

[Dave Peterson]

There are two different listboxes in excel--one from the controltoolbox toolbar
(also used on your userform) and one on the Forms toolbar.

You have to be a little more specific or excel won't know (and may guess the
wrong one).

Private Sub ColumnToList(lstTarget As msforms.ListBox, rngSource As Range)

Got past the confused error. (But I didn't test too much more--too much to
setup!)

 

[rvExcelTip]

Dave, you hit the nail on its head: using the msForms qualifier got m
into the subroutine. I can now work further trying to get the righ
data into the list. Thanks, Rober