It's been several years since I've written VBA, so pretty rusty. I copied this code from elsewhere and it does what I want it to do except I want to add one thing.
I have a Userform with a macro that brings up a Listbox with 5 rows and 3 columns. There is a base product part number in cell C31 on the spreadsheet. The listbox displays the options that can be added to the part number. Column one in my source range is the suffix option code, column two is a description, and column three is the price adder. I've inserted a command button on the spreadsheet called "Select Options" that brings up the listbox. After the options are selected, the box closes (after they press the Select Options button again) and the option codes they selected are written to cell C23 ("ListBoxOutput") - each code is separated by "-". I then have a formula in cell C31 that adds the option codes to the base part number in cell C31. So for example, the base part number may be TDB-7001D, but after selecting their options, the final part number may be TDB-7001D-A-C-X.
I have a base price in cell D31. As the user adds the options, I want their price adders (column 3 in the source range) to be added to the value already in D31. The current macro code looks like:
The Listbox looks like:
I tried to insert this line in yellow for adding the option price adders but it didn't work:
Thoughts?
I have a Userform with a macro that brings up a Listbox with 5 rows and 3 columns. There is a base product part number in cell C31 on the spreadsheet. The listbox displays the options that can be added to the part number. Column one in my source range is the suffix option code, column two is a description, and column three is the price adder. I've inserted a command button on the spreadsheet called "Select Options" that brings up the listbox. After the options are selected, the box closes (after they press the Select Options button again) and the option codes they selected are written to cell C23 ("ListBoxOutput") - each code is separated by "-". I then have a formula in cell C31 that adds the option codes to the base part number in cell C31. So for example, the base part number may be TDB-7001D, but after selecting their options, the final part number may be TDB-7001D-A-C-X.
I have a base price in cell D31. As the user adds the options, I want their price adders (column 3 in the source range) to be added to the value already in D31. The current macro code looks like:
VBA Code:
Sub Rectangle1_Click()
Dim xSelShp As Shape, xSelLst As Variant, I, J As Integer
Dim xV As String
Set xSelShp = ActiveSheet.Shapes(Application.Caller)
Set xLstBox = ActiveSheet.ListBox1
If xLstBox.Visible = False Then
xLstBox.Visible = True
xSelShp.TextFrame2.TextRange.Characters.Text = "Select Options"
xStr = ""
xStr = Range("ListBoxOutput").Value
If xStr <> "" Then
xArr = Split(xStr, "-")
For I = xLstBox.ListCount - 1 To 0 Step -1
xV = xLstBox.List(I)
For J = 0 To UBound(xArr)
If xArr(J) = xV Then
xLstBox.Selected(I) = True
Exit For
End If
Next
Next I
End If
Else
xLstBox.Visible = False
xSelShp.TextFrame2.TextRange.Characters.Text = "Select Options"
For I = xLstBox.ListCount - 1 To 0 Step -1
If xLstBox.Selected(I) = True Then
xSelLst = xLstBox.List(I) & "-" & xSelLst
End If
Next I
If xSelLst <> "" Then
Range("ListBoxOutput") = Mid(xSelLst, 1, Len(xSelLst) - 1)
Else
Range("ListBoxOutput") = ""
End If
End If
End Sub
The Listbox looks like:
I tried to insert this line in yellow for adding the option price adders but it didn't work:
Thoughts?