Passing a ListBox as Parameter

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

scotty718

Thread Starter
Joined
Nov 19, 2010
Messages
185
Hello All,

I have code that I've written based on this link http://www.ozgrid.com/forum/showthread.php?t=34245 and others that I have found link. However, I get a type mismatch on the following line and when I debug and hover over Me.SheetsToExport_LBox is says it = Null

Code:
Functions.Forms_AddItemToListBox Me.SheetsToExport_LBox, lngSheetIndex, rngWkName.Text, rngWkName.offset(, intOffset)
Anyone that can help me solve this conundrum? I'd be very grateful...

The full code is here and is Private Sub on a UserForm that is called from the UserForm_Activate() event
Code:
Private Sub FillListBoxesOnForm()

Dim rngWkName As Range, lngSheetIndex As Long, intOffset As Integer

    
'=== Fill In List Boxes ========
'set offset for which export type is needed
If Me.optClientCopy.Value = True Then intOffset = 2 Else: intOffset = 3
    
lngSheetIndex = 1
For Each rngWkName In UserControls_ws.Range("Exported_Sheets")
    If SheetExists(rngWkName.Text) Then
        Functions_Forms.AddItemToListBox Me.SheetsToExport_LBox, lngSheetIndex, rngWkName.Text, rngWkName.offset(, intOffset)
        If Me.optClientCopy.Value = True Then
            Select Case rngWkName.Text
                Case Is = "A_11_Reclasses", "A_12_M-1s"
                    Functions_Forms.AddItemToListBox Me.lbox_formulastokeep, lngSheetIndex, rngWkName.Text, rngWkName.offset(, 3)
            End Select
        Else
            Functions_Forms.AddItemToListBox Me.lbox_formulastokeep, lngSheetIndex, rngWkName.Text, rngWkName.offset(, 3)
        End If
        lngSheetIndex = lngSheetIndex + 1
    End If 'If SheetExists(rngWkName.Text) Then
Next
'=== Fill In List Boxes Finished ========

End Sub
The Function AddItemToListBox is a separate module. The code is below:

Code:
Public Function ClearListBox(ByVal lbox As MSFORMs.ListBox)

Dim intItemCount As Integer

For intItemCount = 0 To lbox.ListCount - 1
    lbox.RemoveItem 0
Next intItemCount

End Function
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
I cannot read your whole code but I use the follwoing syntax and it works:
Call the function upon opening the form so the list box initializes

Code:
Function Read_TypeCodes()
    Dim rng As Range, l As Long
    Set wsa = Sheets("your sheet's name")
    l = 0
    For Each rng In Range(wsa.Range("A1").Value)
        With Me.tCodeBox
            .AddItem rng.Value
            l = l + 1
        End With
    Next rng
    Me.tCodeBox.ListRows = WorksheetFunction.Min(lMax, l)
End Function
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top