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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Rosen

Active Member
Joined
Dec 1, 2010
Messages
293
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,667
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,344
Messages
5,486,294
Members
407,538
Latest member
kbendelac

This Week's Hot Topics

Top