Multiplication of values based on listbox selections

[leedaly]

Hi, really hope someone can help me with this one, I don't know VB at all, this is a new challenge for me and cant seem to resolve through web searching...

I am using Excel 2016.

So I have learned how to create an ActiveX listbox, how to populate the listbox and turn on MultiSelect.
My listbox currently shows the "Options" Range Column A (below)

My range of selected cells are;
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Options[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Option 1[/TD]
[TD]1.4[/TD]
[/TR]
[TR]
[TD]Option 2[/TD]
[TD]4.2[/TD]
[/TR]
[TR]
[TD]Option 3[/TD]
[TD]2.0[/TD]
[/TR]
[TR]
[TD]Option 4[/TD]
[TD]9.1[/TD]
[/TR]
</tbody>[/TABLE]

What I want to be able to do is when I select say Option 1 and Option 3 or any combination, is to show an output into a specific worksheet cell, the product from the corresponding value of Column B

Eg if I click on Option 1 and Option 3 in the listbox the worksheet cell value would show "2.8" the sum of 1.4 x 2.0

Many thanks in advance for some help on next steps.

 

[Rosen]

Here is some code that might help (assumptions: ListBox is called ListBox1, output is to go into cell A1 of the same spreadsheet)

Option Explicit
Private SelectedFirst As Long
Private SelectedSecond As Long
Private DisableChangeEvent As Boolean
Private Sub ListBox1_Change()
    If DisableChangeEvent Then Exit Sub
    Dim i As Long
    For i = 1 To (ListBox1.ListCount - 1) Step 1
    
        ' WE HAVE SELECTED SOMETHING THAT WASN'T SELECTED BEFORE
        If ListBox1.Selected(i) And Not SelectedFirst = i And Not SelectedSecond = i Then
        
            If SelectedFirst = 0 Then
                
                SelectedFirst = i
            ElseIf SelectedSecond = 0 Then
                
                SelectedSecond = i
            Else
                
                DisableChangeEvent = True
                ListBox1.Selected(SelectedFirst) = False
                DisableChangeEvent = False
                SelectedFirst = SelectedSecond
                SelectedSecond = i
            End If
        
        ' HAVE WE UNSELECTED OUR FIRST SELECTION
        ElseIf ListBox1.Selected(i) = False And SelectedFirst = i Then
            
            ' SECOND SELECTION EXISTS, MOVE TO FIRST SELECTION
            If Not SelectedSecond = 0 Then
            
                SelectedFirst = SelectedSecond
                SelectedSecond = 0
            
            ' ONLY ONE SELECTED, UNSELECT IT
            Else
            
                SelectedFirst = 0
                
            End If
            
        ' HAVE WE UNSELECTED OUR SECOND SELECTION
        ElseIf ListBox1.Selected(i) = False And SelectedSecond = i Then
        
            SelectedSecond = 0
            
        End If
    Next
    
    If Not SelectedFirst = 0 And Not SelectedSecond = 0 Then
        
        Dim Value1 As Double
        Dim Value2 As Double
        
        Value1 = CDbl(ListBox1.List(SelectedFirst, 1))
        Value2 = CDbl(ListBox1.List(SelectedSecond, 1))
        
        Range("A1").Value = Value1 * Value2
    End If
End Sub

Please remember to backup any work before running new code.

Hope this helps!

 

[Fluff]

Another option

Private Sub ListBox1_Change()
   Dim i As Long
   Dim Prd As Double
   
   With Me.ListBox1
      For i = 0 To .ListCount - 1
         If .Selected(i) Then
            If Prd = 0 Then Prd = .list(i, 1) Else Prd = Prd * .list(i, 1)
         End If
      Next i
   End With
   Range("D5").Value = Prd
End Sub