Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: VBA Function to Count ListBox Items Selected

    I am trying to write a function to count items selected in a list box on a form, and pass the form name (if necessary) and list box name to the function. This function will allow me to get the counts for multiple list boxes in my Excel 2003 application. Without this function, I will need to code for each list box where a selected-item-count is done. Not a big deal with only two list boxes--I'm just trying to see if a single function can do it all.
    Here is my code that does not work.
    Code:
    Function Selected_Count(frmName As String, lstBoxName As String) As Integer
    Dim sCount As Integer, i As Integer
    Dim ctl As Control
    
    Set ctl = frmName.Controls(lstBoxName)
        
        With ctl
            For i = 0 To .ListCount - 1
                If .Selected(intIndex) Then sCount = sCount + 1
            Next
        End With
        
        Selected_Count = sCount
    
    End Function
    Jerry
    Last edited by JerryDal; 09-08-11 at 18:36. Reason: clarification

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Here is the solution I worked out to pass a control name to a function in Excel 2003 VBA. A demo of this is attached.

    Code:
    Function Any_Selected(ListBoxName) As String
    'loops through a list box and returns # of items selected
    Dim i As Integer, selCount As Integer
    
        For i = 0 To Controls(ListBoxName).ListCount - 1
            If Controls(ListBoxName).Selected(i) = True Then selCount = selCount + 1
        Next
        
        Any_Selected = CStr(selCount)
        
    End Function
    RESOLVED
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •