Form as Form? or String? (1 Viewer)

[Ken Sheridan]

All I'm trying to do is figure which listbox is visible and then grab that for other events. What I'm struggling with is calling the above function. I've figured out I need to make it a string

By specifically naming the two possible controls your code is very restrictive. The following, on the other hand, allows you to reference any form which contains one visible list box control. The number of list box control's in the form is completely arbitrary form 1 to n, as long as only one is visible at any one time:

Public Function GetList(frm As Form) As Control

    Dim ctrl As Control
 
    For Each ctrl In frm.Controls
        If ctrl.ControlType = acListBox Then
            If ctrl.Visible Then
                Set GetList = ctrl
            End If
        End If
    Next ctrl

End Function

You can then call it by passing a reference to the form into the function, e.g. in the same form's module:

    Dim ctrl As Control
    Set ctrl = GetList(Me)

If you do need the name of the control as a string expression you can return the control object's Name property:

    Debug.Print ctrl.Name

 

[RonPaii]

I forgot vbNewLine is a 2 character sting. Requiring the same treatment as the Null eDelimiterType

Public Enum eDelimiterType
    Public Enum eDelimiterType
    NoDelimiter = 0
    DoubleQuotes = 34
    Octothorpes = 35
    SingleQuotes = 39
End Enum

Public Enum eSeperatorType
    Comma = 44
    Pipe = 124
    SemiColon = 59
    Tilde = 126
    NewLine = 1
End Enum

' ----------------------------------------------------------------
' Procedure Name: fGetLbx
' Purpose: Get array of item in a multiselect listbox
' Procedure Kind: Function
' Procedure Access: Public
' Parameter lbx (ListBox): Your listbox object (ie. Me.MyList)
' Parameter intColumn (Integer): The listbox column to return
' Parameter Seperator (eSeperatorType): character seperating the array values
' Parameter Delimiter (eDelimiterType): Delimiters for array values (ie.Double Quotes or Octothorpes)
' Return Type: Variant
' Author: Moke123
'
' **** NOTE **** Returns Null if no items selected. Use NZ() in calling code to handle nulls
'
' ----------------------------------------------------------------

Public Function fgetLBX(lbx As ListBox, _
    Optional intColumn As Integer = 0, _
    Optional Seperator As eSeperatorType = eSeperatorType.Comma, _
    Optional Delimiter As eDelimiterType = eDelimiterType.NoDelimiter) As Variant

    On Error GoTo fGetLbx_Error
    
    Dim strlist As String, varSelected As Variant, DeLimit As Variant, SepChar As String
    
    if Delimiter = eDelimiterType.NoDelimiter then
        DeLimit = Null
    else
    DeLimit = Chr(Delimiter)
    end if
              
    if Seperator = eSeperatorType.NewLine then
        SepChar = vbNewLine
    else
        SepChar = Chr(Seperator)
    end if