Multiplication of values based on listbox selections

leedaly

New Member
Joined
May 15, 2019
Messages
1
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;
Column AColumn B
OptionsValue
Option 11.4
Option 24.2
Option 32.0
Option 49.1

<tbody>
</tbody>

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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Here is some code that might help (assumptions: ListBox is called ListBox1, output is to go into cell A1 of the same spreadsheet)
Code:
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!
 
Upvote 0
Another option
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top