dcsimg
Results 1 to 6 of 6

Thread: Multiplication of values based on multiple listbox selections

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2019
    Posts
    3

    Multiplication of values based on multiple listbox selections

    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 A
    Options
    Option 1
    Option 2
    Option 3
    Option 4

    Column B
    Corresponding Numerical Value
    1.4
    4.2
    2.0
    9.1

    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.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,896

    Re: Multiplication of values based on multiple listbox selections

    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,217

    Re: Multiplication of values based on multiple listbox selections

    you need to loop through the list items, get the values from the selected items like
    Code:
    Dim tot As Double
    tot = 1
    With ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) Then tot = tot * .List(i, 1)
    Next
    End With
    MsgBox tot
    change name of listbox to suit, note you need to start with a base value of 1, if you use the default value of 0 you total will be 0
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2019
    Posts
    3

    Re: Multiplication of values based on multiple listbox selections

    Hi Westconn1

    Thats a great help, thank you.

    I am just stuck now on how to put the output of the sum into a cell on the worksheet... I have put a linkedcell into the properties, but nothing is showing in the cell.
    I have populated the listbox with option 1 to 4
    Unfortunately, even though I can go into the VB editor... how do I to apply this code to listbox1?... sorry about this but really am new to vba, and not sure if i am doing this right.

    Again thanks for any support.
    Last edited by LeeDaly; May 20th, 2019 at 10:09 AM.

  5. #5

    Thread Starter
    New Member
    Join Date
    May 2019
    Posts
    3

    Re: Multiplication of values based on multiple listbox selections

    Screenshot to show you where i am at... need my hand held on this one. Once I understand I will be able to apply to my little mini project.

    Name:  VB1.jpg
Views: 17
Size:  29.1 KB
    Last edited by LeeDaly; May 20th, 2019 at 10:19 AM.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,217

    Re: Multiplication of values based on multiple listbox selections

    the basic would be
    Code:
    range("b7").value = tot
    in place of the messagebox

    if you are working with multiple worksheets, or the cell for the result is not on the active sheet, then may would have to fully qualify the range, possibly starting like workbook.worksheet.range
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width