Return the number of selected items from a listbox

[JustHooch]

When a user clicks the submit button all information from the form is transferred to tab in my workbook.

The part of this I am having trouble with is the count of selected items from a 3 listboxes. Each listbox needs a separate count going to a different cell.

Start of the transfer:

 Private Sub CommandButtonSubmitScope_Click()
        MultiPage1.Value = 0
        Sheets("Ticket Product Info").Activate
       
[INDENT]Range("B3").Value = TextBoxProductName[/INDENT]
        Range("B5").Value = ComboBoxAffiliation
        Range("B6").Value = ComboBoxLabel
        Range("B7").Value = ComboBoxBrand
        Range("B8").Value = ComboBoxProductType
        Range("B9").Value = ComboBoxProductType2
[INDENT]'etc....[/INDENT]

The last bit of code needs to tranfere the below 3 Listboxes:

1. ListBoxAffiliates
2. ListBoxBOLT
3. ListBoxDTC

Cells they need to feed to on the same tab:

1. K4
2. K5
3. K7

 

[JustHooch]

These are all multiselect Listboxes

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...-number-of-selected-items-from-a-listbox.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[JustHooch]

Understood won't happen again.

 

[JustHooch]

I came up with an alternative solution. Not the most eligant but it works. I created textbox for each listbox to count the items as they are being selected (see the below link). This also made validating that something is select in each list box.

[url]http://www.vbaexpress.com/forum/showthread.php?51428-Count-Selected-Items-in-Multselect-Listbox [/URL]

   'Count of Affiliates Stores selected
    Private Sub ListBoxAffiliates_Change()
    Dim iCount As Long
    Dim i As Long
        iCount = 0
        With Me.ListBoxAffiliates
            For i = 0 To .ListCount - 1
                If .Selected(i) Then iCount = iCount + 1
            Next i
        End With
        If iCount = 1 Then
            Me.TextBoxAffliliatesStoreQTY = iCount
        Else
            Me.TextBoxAffliliatesStoreQTY = iCount
        End If
    End Sub
    
    'Count of BOLT Stores selected
    Private Sub ListBoxBOLT_Change()
    Dim iCount As Long
    Dim i As Long
        iCount = 0
        With Me.ListBoxBOLT
            For i = 0 To .ListCount - 1
                If .Selected(i) Then iCount = iCount + 1
            Next i
        End With
        If iCount = 1 Then
            Me.TextBoxBOLTStoreQTY = iCount
        Else
            Me.TextBoxBOLTStoreQTY = iCount
        End If
    End Sub
    
    'Count of DTC Stores selected
    Private Sub ListBoxDTC_Change()
    Dim iCount As Long
    Dim i As Long
        iCount = 0
        With Me.ListBoxDTC
            For i = 0 To .ListCount - 1
                If .Selected(i) Then iCount = iCount + 1
            Next i
        End With
        If iCount = 1 Then
            Me.TextBoxDTCStoreQTY = iCount
        Else
            Me.TextBoxDTCStoreQTY = iCount
        End If
    End Sub
    
    'When Submit Scope Estimation
     Private Sub CommandButtonSubmitScope_Click()
    
    'Check for Required Fields
        'If Affiliates was selected ensure at least 1 stores is selected
            If FrameAffiliatesStore.Enabled = True Then
                If TextBoxAffliliatesStoreQTY = "" Then Msg = Msg & "No Affiliate stores selected (Pick all that Apply)" & vbLf
                Else
                'Do nothing
            End If
         'If BOLT was selected was selected ensure at least 1 stores is selected
            If FrameAffiliatesStore.Enabled = True Then
                If TextBoxBOLTStoreQTY = "" Then Msg = Msg & "No BOLT stores selected (Pick all that Apply)" & vbLf
                Else
                'Do nothing
            End If


        'If DTC was selected was selected ensure at least 1 stores is selected
            If FrameDTCStore.Enabled = True Then
                If TextBoxDTCStoreQTY = "" Then Msg = Msg & "No DTC stores selected (Pick all that Apply)" & vbLf
                Else
                'Do nothing
            End If
            
        'Create one msg for all error
            If Len(Msg) > 0 Then
                MsgBox Msg
                Exit Sub
            End If
    
    'Make Ticket Tab active
        MultiPage1.Value = 0
        Sheets("Ticket Product Info").Activate

    'Transfer all other information to Ticket Tab
        Range("K4").Value = CDbl(TextBoxAffliliatesStoreQTY)
        Range("K5").Value = CDbl(TextBoxBOLTStoreQTY)
        Range("K6").Value = IIf(CheckBoxConsumer.Value, 1, 0)
        Range("K7").Value = CDbl(TextBoxDTCStoreQTY)
        Range("K8").Value = IIf(CheckBoxMobile.Value, 1, 0)

    'End User on the Product Scope tab
        Application.Visible = True
        Sheets("Ticket Product Scope").Activate
        Me.Hide
    End Sub