VBA / Userform Expert Needed!

[jreilly851]

Hi guys,
I know someone out there can help, and most likely the solution is right under my nose! Here's my problem:

<gs id="599a80ab-3dc0-4be6-9580-6cb172c5fdca" ginger_software_uiphraseguid="b4acff07-67dd-4d48-8ca8-8220b278e8cc" class="GINGER_SOFTWARE_mark">I</gs> have a <gs id="bf461e18-cb18-4c7d-a485-2ecabcdbe0f1" ginger_software_uiphraseguid="1fac60ba-1652-4423-9ee5-351f7c62f0b7" class="GINGER_SOFTWARE_mark"><gs id="83409ed4-08d5-4948-956e-30066b0838d5" ginger_software_uiphraseguid="ee711296-42c5-4261-830c-50f6a1e4327f" class="GINGER_SOFTWARE_mark">userform</gs></gs> created, see the link below, and I'm trying to pull the SELECTED VALUE (you click on 1 line, and it becomes highlighted, or "selected") from the large multi-select listbox (aptly named "listbox1"), and do a <gs id="011ab240-ceb1-470c-93f9-0cbcfa0dbb61" ginger_software_uiphraseguid="1fac60ba-1652-4423-9ee5-351f7c62f0b7" class="GINGER_SOFTWARE_mark"><gs id="8311260b-5993-4dd9-a71d-1c2090200cc2" ginger_software_uiphraseguid="ee711296-42c5-4261-830c-50f6a1e4327f" class="GINGER_SOFTWARE_mark">Vlookup</gs></gs> on a specified sheet "Hierarchy Codes," range B<gs id="cdfee1aa-fd43-4c73-9a16-119e5c11a4bc" ginger_software_uiphraseguid="1fac60ba-1652-4423-9ee5-351f7c62f0b7" class="GINGER_SOFTWARE_mark"><gs id="268732ae-6b8b-45a2-b102-0615f7e3418d" ginger_software_uiphraseguid="ee711296-42c5-4261-830c-50f6a1e4327f" class="GINGER_SOFTWARE_mark">:</gs></gs>C, and populate the result of that <gs id="278b247f-7e8f-4a83-9a20-aeeb63b21792" ginger_software_uiphraseguid="1fac60ba-1652-4423-9ee5-351f7c62f0b7" class="GINGER_SOFTWARE_mark"><gs id="97d2a064-e7cd-4dd0-958b-e25657ebe199" ginger_software_uiphraseguid="ee711296-42c5-4261-830c-50f6a1e4327f" class="GINGER_SOFTWARE_mark">vlookup</gs></gs> into textbox1 over there to the right, <gs id="16d7e388-b1e2-424a-9217-b95f3e44cfca" ginger_software_uiphraseguid="c7371bff-8987-4d43-82cd-a405ef0171c5" class="GINGER_SOFTWARE_mark">labeled</gs> "selected hierarchy last 12 months sales." In <gs id="a6f68644-dc73-49ed-ae8d-528a097ffdd5" ginger_software_uiphraseguid="1fac60ba-1652-4423-9ee5-351f7c62f0b7" class="GINGER_SOFTWARE_mark"><gs id="6dd0d732-18d7-41d9-8a9f-8f937326f7ea" ginger_software_uiphraseguid="ee711296-42c5-4261-830c-50f6a1e4327f" class="GINGER_SOFTWARE_mark">essense</gs></gs>, I'm trying to accomplish....<gs id="592fcb0b-011e-4d3f-ad18-e045f08e98b2" ginger_software_uiphraseguid="ee711296-42c5-4261-830c-50f6a1e4327f" class="GINGER_SOFTWARE_mark">.</gs><gs id="e5b8a7cc-1b96-450e-bed0-adde427405f0" ginger_software_uiphraseguid="1fac60ba-1652-4423-9ee5-351f7c62f0b7" class="GINGER_SOFTWARE_mark"><gs id="d4d300e9-250d-4e40-aa57-f8a83bdc1015" ginger_software_uiphraseguid="ee711296-42c5-4261-830c-50f6a1e4327f" class="GINGER_SOFTWARE_mark">vlookup</gs></gs><gs id="5d769b5b-4270-4523-bae6-4397d669af20" ginger_software_uiphraseguid="1fac60ba-1652-4423-9ee5-351f7c62f0b7" class="GINGER_SOFTWARE_mark"><gs id="849ab024-f341-460c-a45e-1a22605ce9d5" ginger_software_uiphraseguid="ee711296-42c5-4261-830c-50f6a1e4327f" class="GINGER_SOFTWARE_mark">(</gs></gs>something here to extract the <gs id="e58d8f44-806f-4b80-ac7c-2f9def8035ca" ginger_software_uiphraseguid="1fac60ba-1652-4423-9ee5-351f7c62f0b7" class="GINGER_SOFTWARE_mark"><gs id="1e3eae1a-abde-4bf9-a732-da8dd725aa5d" ginger_software_uiphraseguid="ee711296-42c5-4261-830c-50f6a1e4327f" class="GINGER_SOFTWARE_mark">listbox</gs></gs> selected text, Sheet<gs id="f29df8aa-cbb0-434b-bcc8-a3de272527be" ginger_software_uiphraseguid="1fac60ba-1652-4423-9ee5-351f7c62f0b7" class="GINGER_SOFTWARE_mark"><gs id="c10f621c-841b-4c19-8314-29bd67c3bfe4" ginger_software_uiphraseguid="ee711296-42c5-4261-830c-50f6a1e4327f" class="GINGER_SOFTWARE_mark">(</gs></gs>"hierarchy codes") B<gs id="b531a01e-f049-47d9-a416-c53d20efbd52" ginger_software_uiphraseguid="1fac60ba-1652-4423-9ee5-351f7c62f0b7" class="GINGER_SOFTWARE_mark"><gs id="99fdba71-a735-43d7-a183-093101741368" ginger_software_uiphraseguid="ee711296-42c5-4261-830c-50f6a1e4327f" class="GINGER_SOFTWARE_mark">:</gs></gs>C, 2, false)

The following textbox2, labeled "Total account sales last 12 months," needs to be populated by doing some kind of a <gs id="d9c1af21-0dda-4eba-aadf-f2bea7ebb892" ginger_software_uiphraseguid="39a58848-3d5d-4144-9f57-83d5026044ff" class="GINGER_SOFTWARE_mark"><gs id="67e04707-d34f-4118-9e7b-84d0205c608e" ginger_software_uiphraseguid="e955dec4-0bc3-4d13-9f71-6a8da13f39a3" class="GINGER_SOFTWARE_mark">worksheetfunction</gs></gs><gs id="b620fc98-340f-40ff-bbf5-72447dc985c0" ginger_software_uiphraseguid="39a58848-3d5d-4144-9f57-83d5026044ff" class="GINGER_SOFTWARE_mark"><gs id="c9b9d890-fd07-4148-ae6f-fb60227dd105" ginger_software_uiphraseguid="e955dec4-0bc3-4d13-9f71-6a8da13f39a3" class="GINGER_SOFTWARE_mark">.</gs></gs><gs id="5b2b7a83-ad16-4c38-9c02-69483863322f" ginger_software_uiphraseguid="39a58848-3d5d-4144-9f57-83d5026044ff" class="GINGER_SOFTWARE_mark"><gs id="0175c117-966f-4d14-a2ef-83ee25fe0e3c" ginger_software_uiphraseguid="e955dec4-0bc3-4d13-9f71-6a8da13f39a3" class="GINGER_SOFTWARE_mark">sum</gs></gs> code by summing up the total of column M on sheet "Account Discounts Raw."

The third and final textbox there to the right simply needs to divide the result of textbox1 by the result of textbox2 to give you the % of total sales.

https://drive.google.com/file/d/0Bx6mAgyyvtrQNE1KVXJ3VXJEOXc/view?usp=sharing

Here is the code I've played around with for the last 24 hours or so.....

​

Private Sub Listbox1_change<gs id="522bf4c5-90d1-4e83-bd3e-d766c8863452" ginger_software_uiphraseguid="4bd5f1f7-e0f0-4e2e-9565-a2b6639e69f9" class="GINGER_SOFTWARE_mark">(</gs>)
Dim ws As Worksheet: Set <gs id="b0854f68-2432-4baa-a167-d5a7860998cf" ginger_software_uiphraseguid="d32c1b23-95d9-437e-9d6a-978a4f0f392e" class="GINGER_SOFTWARE_mark">ws</gs> = Sheets<gs id="93008d08-5705-485c-8d2c-18d21eb06971" ginger_software_uiphraseguid="d32c1b23-95d9-437e-9d6a-978a4f0f392e" class="GINGER_SOFTWARE_mark">(</gs>"Hierarchy Codes")
Dim <gs id="802f61fc-03f3-4195-9a15-b080ec425c07" ginger_software_uiphraseguid="f42cc819-7f8e-4f78-85d5-2cdb3da68894" class="GINGER_SOFTWARE_mark">rngLook</gs> As Range: Set <gs id="64773c85-b6fd-4a2c-ab21-f8a2ff621d83" ginger_software_uiphraseguid="f42cc819-7f8e-4f78-85d5-2cdb3da68894" class="GINGER_SOFTWARE_mark">rngLook</gs> = <gs id="5a849e27-6298-4a3f-a4cd-7e3bcda3d0eb" ginger_software_uiphraseguid="f42cc819-7f8e-4f78-85d5-2cdb3da68894" class="GINGER_SOFTWARE_mark">ws</gs>.Range<gs id="5bd4e7ff-25a8-4fc4-9f10-a7bebc2e5085" ginger_software_uiphraseguid="f42cc819-7f8e-4f78-85d5-2cdb3da68894" class="GINGER_SOFTWARE_mark">(</gs>"B<gs id="21072260-9197-40b9-bf24-5817fb4b0751" ginger_software_uiphraseguid="f42cc819-7f8e-4f78-85d5-2cdb3da68894" class="GINGER_SOFTWARE_mark">:</gs>C")
Dim <gs id="ddb26647-c9c5-4520-8503-458380db3fd8" ginger_software_uiphraseguid="40f62bb3-557a-459f-9a02-474224901ebb" class="GINGER_SOFTWARE_mark">currName</gs> As String
Dim <gs id="a5d5dd4d-6262-49f0-ba4c-cd69b5e177e2" ginger_software_uiphraseguid="6b25f504-4ab7-4295-9eb9-68d02b830905" class="GINGER_SOFTWARE_mark">cellNum</gs> As Variant
Dim wss As Worksheet: Set <gs id="a5a017f4-1990-4c57-8b7c-f86d5d97a484" ginger_software_uiphraseguid="4bf819b5-02bf-4264-9722-e41fb48ccc8d" class="GINGER_SOFTWARE_mark">wss</gs> = Sheets<gs id="1f534bca-7ec1-4958-9235-90b519099e60" ginger_software_uiphraseguid="4bf819b5-02bf-4264-9722-e41fb48ccc8d" class="GINGER_SOFTWARE_mark">(</gs>"Account Discounts Raw")
Dim TotSales As String
<gs id="c3c1c8bb-0ca2-4ef1-8225-2e6175875b20" ginger_software_uiphraseguid="934ebed9-f503-4cee-87ef-e80798fbbf6c" class="GINGER_SOFTWARE_mark">TotSales</gs> = Application.WorksheetFunction.Sum<gs id="cae9b523-f532-4005-ac0c-b489e8427089" ginger_software_uiphraseguid="934ebed9-f503-4cee-87ef-e80798fbbf6c" class="GINGER_SOFTWARE_mark">(</gs>Sheets<gs id="a4f3ccb9-32db-42db-9d4f-51ccfca734e8" ginger_software_uiphraseguid="934ebed9-f503-4cee-87ef-e80798fbbf6c" class="GINGER_SOFTWARE_mark">(</gs>"Account Discounts Raw"), "M<gs id="f57c541e-1e65-4ce2-80a6-edebe91c98ae" ginger_software_uiphraseguid="934ebed9-f503-4cee-87ef-e80798fbbf6c" class="GINGER_SOFTWARE_mark">:</gs>M")


'<gs id="0b253c73-bf41-4819-a55b-269c630c9b1f" ginger_software_uiphraseguid="48e07d2e-b8e0-4488-bd75-5f7aa513fec5" class="GINGER_SOFTWARE_mark"><gs id="0ecb511b-4f89-42b0-9fac-f602c903dd25" ginger_software_uiphraseguid="cf472096-940a-4251-a915-ce9e1c130497" class="GINGER_SOFTWARE_mark">within</gs></gs> a loop
<gs id="f127f914-5e71-4598-afb7-b30a6d6cc284" ginger_software_uiphraseguid="c30cd374-dc1d-4abb-b936-74a9c5469663" class="GINGER_SOFTWARE_mark"><gs id="30d3f2b9-687b-4bbe-aa02-f587d7b35393" ginger_software_uiphraseguid="33b3f9a8-aff0-43cb-843a-b9ef060e0ebd" class="GINGER_SOFTWARE_mark">currName</gs></gs> = Listbox1.Value


<gs id="b3dcc8fe-6c95-4a8f-8ffe-12098d04d945" ginger_software_uiphraseguid="863ac4c8-ba44-4a4e-9e45-0651787be438" class="GINGER_SOFTWARE_mark"><gs id="9dfd464d-15ad-458e-8df4-e6883ba67efb" ginger_software_uiphraseguid="6acb7e76-719e-4f9b-9caa-8946c95b6500" class="GINGER_SOFTWARE_mark">cellNum</gs></gs> = Application<gs id="d5038416-bde4-4758-af36-a9685b300bf8" ginger_software_uiphraseguid="6acb7e76-719e-4f9b-9caa-8946c95b6500" class="GINGER_SOFTWARE_mark">.</gs>VLookup<gs id="47e0fcf0-af6e-413b-b6df-ccb01a674e2d" ginger_software_uiphraseguid="863ac4c8-ba44-4a4e-9e45-0651787be438" class="GINGER_SOFTWARE_mark"><gs id="49aeb27e-9ac2-4913-8aa7-a5da646660e2" ginger_software_uiphraseguid="6acb7e76-719e-4f9b-9caa-8946c95b6500" class="GINGER_SOFTWARE_mark">(</gs></gs><gs id="9f08b752-7a10-4e11-81f1-68d7d88b0bb9" ginger_software_uiphraseguid="863ac4c8-ba44-4a4e-9e45-0651787be438" class="GINGER_SOFTWARE_mark"><gs id="dd8c8668-3824-47d1-91e7-df73a23f36bb" ginger_software_uiphraseguid="6acb7e76-719e-4f9b-9caa-8946c95b6500" class="GINGER_SOFTWARE_mark">currName</gs></gs>, <gs id="1d75a7cb-b38c-4e43-8cd1-df35d7a265d4" ginger_software_uiphraseguid="863ac4c8-ba44-4a4e-9e45-0651787be438" class="GINGER_SOFTWARE_mark"><gs id="6d712ae6-799f-4502-9ee3-711880717c68" ginger_software_uiphraseguid="6acb7e76-719e-4f9b-9caa-8946c95b6500" class="GINGER_SOFTWARE_mark">rngLook</gs></gs>, 2, True)
If IsError<gs id="d81c0ee6-e80f-4531-9933-a631771df40b" ginger_software_uiphraseguid="c6ac245d-4603-49e9-91b1-8d79561fa4df" class="GINGER_SOFTWARE_mark"><gs id="05bcb7ad-ec10-4e44-a6e5-313ba186a20f" ginger_software_uiphraseguid="20a595ad-0e07-4504-9ce2-c9a148eaa3d2" class="GINGER_SOFTWARE_mark">(</gs></gs><gs id="73425144-b78a-4eb6-a89e-67aad81f456f" ginger_software_uiphraseguid="c6ac245d-4603-49e9-91b1-8d79561fa4df" class="GINGER_SOFTWARE_mark"><gs id="ca6fc4e6-dc1c-466d-8eae-3d498ea25693" ginger_software_uiphraseguid="20a595ad-0e07-4504-9ce2-c9a148eaa3d2" class="GINGER_SOFTWARE_mark">cellNum</gs></gs>) Then
 MsgBox "no match"
Else
    TextBox1.Value = <gs id="221465c7-819a-4e1b-9df4-8d8778dff742" ginger_software_uiphraseguid="f5d6d6eb-ffd6-485f-a5d1-fd8a665f72e0" class="GINGER_SOFTWARE_mark"><gs id="f4b9f6b6-f317-4c3a-bc85-e74502b94f6d" ginger_software_uiphraseguid="2834b02e-a797-46f5-8afd-0e66522933dd" class="GINGER_SOFTWARE_mark">cellNum</gs></gs>
    TextBox2.Value = TotSales
    TextBox3.Value =  
End If
End Sub

Thank you in advance!

 

[Jerry Sullivan]

Hi jreilly851,

There's a few things that need to be fixed.

1. If you are using a listbox with multi-selection, you'll need to step through each item to find the selected ones.

You can't use this syntax even if only one item has been selected:

currName = Listbox1.Value

Is there any reason you want to make the listbox have multi-selection? Your description only mentions doing a Vlookup on a single value.
If you choose that because you eventually want to be able to sum multiple accounts, I'd encourage you to get your code working first with just one listbox selection- then extend it's features.

2. Once you are successfully assigning the selected item to the variable currName, reconsider whether the last argument in the Vlookup call should be True (approximate match) or False (exact match- as described in your OP).

cellNum = Application.VLookup(currName, rngLook, 2, True)

3. This syntax for the Sum function is incorrect.

TotSales = Application.WorksheetFunction.Sum(Sheets("Account Discounts Raw"), "M:M")

Try instead...

TotSales = Application.WorksheetFunction.Sum(wss.Range("M:M"))

 

[mikerickson]

Another approach depends on how the list box is loaded.
If the list comes from the left column of the VLookup data range, you can use a multi-column listbox with hidden columns to store the lookup value. The ListBox.List becomes a (0-based) copy of the workbook.

Private Sub ListBox1_Change()
    Const Delimiter As String = ","
    Dim selectedString As String
    Dim i As Long
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                selectedString = selectedString & Delimiter & .List(i, 1)
            End If
        Next i
    End With
    TextBox1.Text = Mid(selectedString, 2)
End Sub

Private Sub UserForm_Initialize()
    With Sheet1.Range("B:C")
        ListBox1.ColumnCount = .Columns.Count
        With Range(.Cells(1, .Columns.Count), .Cells(Rows.Count, 1).End(xlUp))
            ListBox1.List = .Value
        End With
    End With
    With ListBox1
       .MultiSelect = fmMultiSelectMulti
        .ColumnWidths = Replace(String(.ColumnCount, "0"), "0", ";0")
    End With
End Sub

 

[jreilly851]

</gs>Hi jreilly851,

There's a few things that need to be fixed.

1. If you are using a <gs id="2ae5d082-8963-484b-8913-1a8d7e73f587" ginger_software_uiphraseguid="4a704f69-e4d0-47ee-ae83-c90d0f535e2a" class="GINGER_SOFTWARE_mark">listbox</gs> with multi-selection, you'll need to step through each item to find the selected ones.

You can't use this syntax even if only one item has been selected:
[CODE<gs id="24151881-eb6e-4b70-89d9-7189daab644a" ginger_software_uiphraseguid="2c7a5be0-fcda-453d-b575-0c713a2cdf04" class="GINGER_SOFTWARE_mark">]</gs><gs id="0869e1eb-dcf1-49bf-921b-7bdd19a2c24a" ginger_software_uiphraseguid="2c7a5be0-fcda-453d-b575-0c713a2cdf04" class="GINGER_SOFTWARE_mark">currName</gs> = Listbox1.Value<gs id="15953c0d-aa4f-47b1-b3d3-6feb5437a144" ginger_software_uiphraseguid="2c7a5be0-fcda-453d-b575-0c713a2cdf04" class="GINGER_SOFTWARE_mark">[</gs>/CODE]

Is there any reason you want to make the <gs id="1c051f62-e55b-4278-a04f-144b3b88ebb5" ginger_software_uiphraseguid="b97498cc-09c8-4535-82da-ef3545f1aca5" class="GINGER_SOFTWARE_mark">listbox</gs> have multi-selection? Your description only mentions doing a <gs id="aff4555e-20b4-47fd-86a5-3043a246894f" ginger_software_uiphraseguid="972d63de-bbc0-48da-ba8f-518d5897686e" class="GINGER_SOFTWARE_mark">Vlookup</gs> on a single value.
If you choose that because you eventually want to be able to sum multiple accounts, I'd encourage you to get your code working first with just one <gs id="4127547f-71fb-4e5a-a059-fd0176435eb3" ginger_software_uiphraseguid="47368773-e502-4762-91b7-a232351c34c5" class="GINGER_SOFTWARE_mark">listbox</gs> selection- then extend <gs id="98397b56-e954-4d89-825a-4f4c69337aec" ginger_software_uiphraseguid="47368773-e502-4762-91b7-a232351c34c5" class="GINGER_SOFTWARE_mark">it's</gs> features.

2. Once you <gs id="936fe0b2-6548-47d3-859d-af22060ff0b7" ginger_software_uiphraseguid="9f2815d5-0031-4de2-bc5e-2dc1b6a3585f" class="GINGER_SOFTWARE_mark">are successfully assigning</gs> the selected item to the variable <gs id="b4290770-da24-4d7c-a85d-90451ad0317e" ginger_software_uiphraseguid="9f2815d5-0031-4de2-bc5e-2dc1b6a3585f" class="GINGER_SOFTWARE_mark">currName</gs>, reconsider whether the last argument in the Vlookup call should be True (approximate match) or False (exact match- as described in your OP).

cellNum = Application.VLookup(currName, rngLook, 2, True)

3. This syntax for the Sum function is incorrect.
[CODE<gs id="bac311ad-da2e-4946-b05e-a70d657eefe3" ginger_software_uiphraseguid="569327f7-fed1-49de-a013-b8b8090cafaf" class="GINGER_SOFTWARE_mark">]</gs>TotSales = Application<gs id="042fa762-f1fd-4eba-9e76-9d26b8d347b8" ginger_software_uiphraseguid="569327f7-fed1-49de-a013-b8b8090cafaf" class="GINGER_SOFTWARE_mark">.</gs>WorksheetFunction<gs id="1531851f-be1a-44c9-a31d-a53a42041135" ginger_software_uiphraseguid="569327f7-fed1-49de-a013-b8b8090cafaf" class="GINGER_SOFTWARE_mark">.</gs>Sum<gs id="ecce5c99-5d62-4d02-b9d5-d29e8b37b224" ginger_software_uiphraseguid="569327f7-fed1-49de-a013-b8b8090cafaf" class="GINGER_SOFTWARE_mark">(</gs>Sheets<gs id="2b180c8b-7389-4d1e-9980-c03cedaacd8a" ginger_software_uiphraseguid="569327f7-fed1-49de-a013-b8b8090cafaf" class="GINGER_SOFTWARE_mark">(</gs>"Account Discounts Raw"), "M<gs id="2b6aaf6b-8f8f-4199-abb9-360cb3ba73b2" ginger_software_uiphraseguid="569327f7-fed1-49de-a013-b8b8090cafaf" class="GINGER_SOFTWARE_mark">:</gs>M"<gs id="020f5a6d-961b-4644-b986-49d5607357f1" ginger_software_uiphraseguid="569327f7-fed1-49de-a013-b8b8090cafaf" class="GINGER_SOFTWARE_mark">)</gs>[/CODE]

Try instead...
[CODE<gs id="b8a10625-5434-47ae-b8f7-764ac2d85291" ginger_software_uiphraseguid="dd22dd91-691c-4db7-9199-6656bb1db848" class="GINGER_SOFTWARE_mark">]</gs>TotSales = Application<gs id="96cd8404-053f-4a56-abee-f6f7283e2a66" ginger_software_uiphraseguid="dd22dd91-691c-4db7-9199-6656bb1db848" class="GINGER_SOFTWARE_mark">.</gs>WorksheetFunction<gs id="505e31e0-f915-44d6-91ff-3e6673b52b75" ginger_software_uiphraseguid="dd22dd91-691c-4db7-9199-6656bb1db848" class="GINGER_SOFTWARE_mark">.</gs>Sum<gs id="ca144424-e3f4-4453-8388-067bd35af131" ginger_software_uiphraseguid="dd22dd91-691c-4db7-9199-6656bb1db848" class="GINGER_SOFTWARE_mark">(</gs><gs id="e9cc481b-5d9c-4ffb-b2f2-66654f5cc89d" ginger_software_uiphraseguid="dd22dd91-691c-4db7-9199-6656bb1db848" class="GINGER_SOFTWARE_mark">wss</gs><gs id="b819fa46-6f44-4371-8300-cc3a5c6043bf" ginger_software_uiphraseguid="dd22dd91-691c-4db7-9199-6656bb1db848" class="GINGER_SOFTWARE_mark">.</gs>Range<gs id="e5e7ce71-3176-4aeb-9576-7fa09d6c1e60" ginger_software_uiphraseguid="dd22dd91-691c-4db7-9199-6656bb1db848" class="GINGER_SOFTWARE_mark">(</gs>"M<gs id="b71c3c57-1c5b-42f5-a9e8-e6714e0d1888" ginger_software_uiphraseguid="dd22dd91-691c-4db7-9199-6656bb1db848" class="GINGER_SOFTWARE_mark">:</gs>M")<gs id="2ddf315a-d7e5-45c8-b677-0808bc62b9aa" ginger_software_uiphraseguid="dd22dd91-691c-4db7-9199-6656bb1db848" class="GINGER_SOFTWARE_mark">)</gs>[/CODE<gs id="e1985f9c-eab5-45cb-8c08-3b249f5676d3" ginger_software_uiphraseguid="dd22dd91-691c-4db7-9199-6656bb1db848" class="GINGER_SOFTWARE_mark">]</gs>

Hi Jerry,

Thanks for the feedback. I DO actually need this to be a <gs id="705c3ab4-f0ce-47da-b72c-fdc3f646f708" ginger_software_uiphraseguid="8a78a36c-191d-4c10-a339-351f06e5a255" class="GINGER_SOFTWARE_mark">multiselect</gs> - more often than not, users will select multiple lines when running this <gs id="9e1afe53-665f-44c5-9c78-e18d0c53190c" ginger_software_uiphraseguid="8a78a36c-191d-4c10-a339-351f06e5a255" class="GINGER_SOFTWARE_mark">userform</gs>. I understand I am introducing a <gs id="a4519612-ed68-4f72-b8b5-0fbcd2a76a6d" ginger_software_uiphraseguid="67cae688-9acf-433c-bea4-803d4d981135" class="GINGER_SOFTWARE_mark">curveball</gs> in that I want to show data points for a single line to the right, when in fact, multiple lines could be selected at any given time. I'm open to suggestions, but I really need to keep the <gs id="8bc28e23-162a-437b-a3f2-9f6c361d72cd" ginger_software_uiphraseguid="ec12ff75-ece0-4199-9c17-8cb36b2ff53c" class="GINGER_SOFTWARE_mark">multiselect</gs> option in the <gs id="6611acee-dfce-4036-b483-5a104cb7fcef" ginger_software_uiphraseguid="ec12ff75-ece0-4199-9c17-8cb36b2ff53c" class="GINGER_SOFTWARE_mark">listbox</gs> by any means possible. I'm going to try the hidden columns suggestion from another comment, but I like your thoughts on simplifying back down to a single select and smoothing out the basic functionality before expanding the features out.

Thanks again!
Jonathan

 

[jreilly851]

</gs>Another approach depends on how the list box is loaded.
If the list comes from the left column of the <gs id="e7ff3d57-d592-4a1e-976c-17f6c2e5bc0b" ginger_software_uiphraseguid="6cea9fea-a2bd-4306-8243-f6f59773eb80" class="GINGER_SOFTWARE_mark">VLookup</gs> data range, you can use a multi-column listbox with hidden columns to store the lookup value. The ListBox<gs id="127f089c-11bb-4965-aaea-5e1296d5508a" ginger_software_uiphraseguid="145c4483-18d4-4445-9c72-5930a9f5f706" class="GINGER_SOFTWARE_mark">.</gs>List becomes a (0-based) copy of the workbook.


[CODE<gs id="0e508672-9154-45ee-814b-5ff8c69183c2" ginger_software_uiphraseguid="548bcca9-f2ea-40a5-9702-1ad4fc02d66b" class="GINGER_SOFTWARE_mark">]</gs>Private Sub ListBox1_Change<gs id="8b12fedb-4866-4e9c-92b5-5b1f59e0ab21" ginger_software_uiphraseguid="548bcca9-f2ea-40a5-9702-1ad4fc02d66b" class="GINGER_SOFTWARE_mark">(</gs>)
<gs id="f9845b40-d626-4f20-9190-b9f4ba4796af" ginger_software_uiphraseguid="0f26fa51-c029-4323-8621-367ba0b15dce" class="GINGER_SOFTWARE_mark">Const</gs> Delimiter As String = ","
Dim <gs id="0aaeef29-c59f-4009-ad8d-9acfc128692d" ginger_software_uiphraseguid="589cdc62-de5f-4b78-b95b-2032171ebfe4" class="GINGER_SOFTWARE_mark">selectedString</gs> As String
Dim <gs id="a58a85d0-4b48-4a9a-ba53-d1472a46e06b" ginger_software_uiphraseguid="84ae46db-059b-4458-ab4c-387e362d3bc9" class="GINGER_SOFTWARE_mark">i</gs> As Long
With ListBox1
For <gs id="26365c88-b2f4-4851-a6ed-d33b399c60b8" ginger_software_uiphraseguid="279cd65a-5f21-43b1-9b7f-2e249dc648a7" class="GINGER_SOFTWARE_mark">i</gs> = 0 To<gs id="69a6fec0-2314-4ef4-8eb4-49afe0ba6f9e" ginger_software_uiphraseguid="279cd65a-5f21-43b1-9b7f-2e249dc648a7" class="GINGER_SOFTWARE_mark"> .</gs><gs id="6633c90c-6255-4f11-91d7-b0bd29cdfbda" ginger_software_uiphraseguid="279cd65a-5f21-43b1-9b7f-2e249dc648a7" class="GINGER_SOFTWARE_mark">ListCount</gs> - 1
If<gs id="04661d9b-9f3a-46c8-af23-772058d3b91b" ginger_software_uiphraseguid="e44f29d3-bba1-4fca-933c-b6d3ff580835" class="GINGER_SOFTWARE_mark"> .</gs>Selected<gs id="47b41f77-ba3e-4e6a-8f7b-bd40ee5f3824" ginger_software_uiphraseguid="e44f29d3-bba1-4fca-933c-b6d3ff580835" class="GINGER_SOFTWARE_mark">(</gs><gs id="aa3dff3d-6997-4177-8b19-becfb6f3ec24" ginger_software_uiphraseguid="e44f29d3-bba1-4fca-933c-b6d3ff580835" class="GINGER_SOFTWARE_mark">i</gs>) Then
<gs id="253c3415-168c-466e-a183-209eb27f86f4" ginger_software_uiphraseguid="3cae2012-7db8-4b97-be39-68c29337388f" class="GINGER_SOFTWARE_mark">selectedString</gs> = <gs id="198c2d78-a0c6-4d9d-9612-0b05cdb1f333" ginger_software_uiphraseguid="3cae2012-7db8-4b97-be39-68c29337388f" class="GINGER_SOFTWARE_mark">selectedString</gs> & Delimiter &<gs id="27caf0c6-f99e-4788-aa48-9bc41787399d" ginger_software_uiphraseguid="3cae2012-7db8-4b97-be39-68c29337388f" class="GINGER_SOFTWARE_mark"> .</gs>List<gs id="9ee047d4-275e-431a-87fa-1c67df3095e4" ginger_software_uiphraseguid="3cae2012-7db8-4b97-be39-68c29337388f" class="GINGER_SOFTWARE_mark">(</gs><gs id="9db417df-80fb-4b0b-ac1c-2b2886eda2c0" ginger_software_uiphraseguid="3cae2012-7db8-4b97-be39-68c29337388f" class="GINGER_SOFTWARE_mark">i</gs>, 1)
End If
Next <gs id="062df44f-4fe6-419b-9e06-9e00efc1261e" ginger_software_uiphraseguid="be400624-5ed7-4b9a-a98d-0e0d265d91aa" class="GINGER_SOFTWARE_mark">i</gs>
End With
TextBox1.Text = Mid<gs id="0aef79df-ef1a-4d46-81d4-64f3cc2259c7" ginger_software_uiphraseguid="800ee462-a5dc-405f-95ae-4ff4aee1a2a8" class="GINGER_SOFTWARE_mark">(</gs><gs id="eca6e0f4-4bac-4975-b55c-d2a65840e835" ginger_software_uiphraseguid="800ee462-a5dc-405f-95ae-4ff4aee1a2a8" class="GINGER_SOFTWARE_mark">selectedString</gs>, 2)
End Sub

Private Sub UserForm_Initialize<gs id="f603b9f8-5056-488f-9f3f-4fc6eb435321" ginger_software_uiphraseguid="747e21da-c727-469f-badd-744755a807c2" class="GINGER_SOFTWARE_mark">(</gs>)
With Sheet1.Range<gs id="bbc8c2e4-9abc-4ea7-83e5-818a0d1cb363" ginger_software_uiphraseguid="55119004-6bc3-408c-84ec-bb20a3e0c6a1" class="GINGER_SOFTWARE_mark">(</gs>"B<gs id="da7a76d6-398e-4707-969a-319be009a0f1" ginger_software_uiphraseguid="55119004-6bc3-408c-84ec-bb20a3e0c6a1" class="GINGER_SOFTWARE_mark">:</gs>C")
ListBox1.ColumnCount =<gs id="63a601fe-3115-48b8-b385-05aff21ea939" ginger_software_uiphraseguid="7b1d9c7a-1c38-4542-a788-eb4d0a3dbe49" class="GINGER_SOFTWARE_mark"> .</gs>Columns<gs id="ba0d7010-5728-44f6-a55d-b6076e4ef27c" ginger_software_uiphraseguid="7b1d9c7a-1c38-4542-a788-eb4d0a3dbe49" class="GINGER_SOFTWARE_mark">.</gs>Count
With Range<gs id="14b7919c-1db2-4a9d-bdd9-b7fb3be84614" ginger_software_uiphraseguid="47532c51-b898-4348-9b86-af3290f7e0af" class="GINGER_SOFTWARE_mark">(</gs><gs id="257af2af-d9c4-4ec2-b0e1-b424e0680634" ginger_software_uiphraseguid="47532c51-b898-4348-9b86-af3290f7e0af" class="GINGER_SOFTWARE_mark">.</gs>Cells<gs id="98721935-0105-4f8e-b16d-48db46a6e9de" ginger_software_uiphraseguid="47532c51-b898-4348-9b86-af3290f7e0af" class="GINGER_SOFTWARE_mark">(</gs>1, .Columns<gs id="97d9b7df-54cf-4b03-bce7-48aee6586587" ginger_software_uiphraseguid="47532c51-b898-4348-9b86-af3290f7e0af" class="GINGER_SOFTWARE_mark">.</gs>Count), .Cells<gs id="fdac96d3-c75d-496f-975a-016ecf28aeda" ginger_software_uiphraseguid="47532c51-b898-4348-9b86-af3290f7e0af" class="GINGER_SOFTWARE_mark">(</gs>Rows<gs id="c7659f5e-92fb-4882-99b9-0cece96f07d0" ginger_software_uiphraseguid="47532c51-b898-4348-9b86-af3290f7e0af" class="GINGER_SOFTWARE_mark">.</gs>Count, 1)<gs id="61e88610-9e4d-46e2-9ca2-182ee0e7dcb8" ginger_software_uiphraseguid="47532c51-b898-4348-9b86-af3290f7e0af" class="GINGER_SOFTWARE_mark">.</gs>End<gs id="44086f62-9f5e-4895-aac8-337f775a66ae" ginger_software_uiphraseguid="47532c51-b898-4348-9b86-af3290f7e0af" class="GINGER_SOFTWARE_mark">(</gs>xlUp))
ListBox1.List =<gs id="e2d3dc2c-6e64-4d19-9008-18936b7ca88a" ginger_software_uiphraseguid="7e76d031-5740-4f08-9946-cf19a02270bb" class="GINGER_SOFTWARE_mark"> .</gs>Value
End With
End With
With ListBox1
<gs id="a57dc4bf-4d02-4e6d-b242-dc138cc940af" ginger_software_uiphraseguid="1184d10f-51f1-4e41-b923-fe2120b1f9fe" class="GINGER_SOFTWARE_mark">.</gs><gs id="ed9d5977-1ef0-483a-9dbb-63af5f919f80" ginger_software_uiphraseguid="1184d10f-51f1-4e41-b923-fe2120b1f9fe" class="GINGER_SOFTWARE_mark">MultiSelect</gs> = <gs id="9c612184-5115-4c00-b9f1-ff903c646481" ginger_software_uiphraseguid="1184d10f-51f1-4e41-b923-fe2120b1f9fe" class="GINGER_SOFTWARE_mark">fmMultiSelectMulti</gs>
<gs id="6b186749-abc2-4f52-9263-222d3322d4d7" ginger_software_uiphraseguid="823aa64a-3ce8-4937-b20d-2825ee0cdca0" class="GINGER_SOFTWARE_mark">.</gs><gs id="29c55055-0c92-44d7-be6c-cac915daa8fb" ginger_software_uiphraseguid="823aa64a-3ce8-4937-b20d-2825ee0cdca0" class="GINGER_SOFTWARE_mark">ColumnWidths</gs> = Replace<gs id="ab6237ac-4634-4371-8db1-9506ae985bbc" ginger_software_uiphraseguid="823aa64a-3ce8-4937-b20d-2825ee0cdca0" class="GINGER_SOFTWARE_mark">(</gs>String<gs id="554f70e3-de2e-40c6-b56d-b3dcba29d6ad" ginger_software_uiphraseguid="823aa64a-3ce8-4937-b20d-2825ee0cdca0" class="GINGER_SOFTWARE_mark">(</gs><gs id="dace8510-5305-418e-823a-59f8253f0c1e" ginger_software_uiphraseguid="823aa64a-3ce8-4937-b20d-2825ee0cdca0" class="GINGER_SOFTWARE_mark">.</gs>ColumnCount, "0"), "0", "<gs id="4220a112-ebb7-42c3-8983-c21a72b7e63f" ginger_software_uiphraseguid="823aa64a-3ce8-4937-b20d-2825ee0cdca0" class="GINGER_SOFTWARE_mark">;</gs>0")
End With
End Sub<gs id="ddcc6760-9609-49f1-bc04-b1db649b3d47" ginger_software_uiphraseguid="c2763460-a686-4817-b628-dabda62dc1e5" class="GINGER_SOFTWARE_mark">[</gs>/CODE<gs id="527382b9-2019-42d4-9c27-a5a17eef33db" ginger_software_uiphraseguid="c2763460-a686-4817-b628-dabda62dc1e5" class="GINGER_SOFTWARE_mark">]</gs>

Hi Mikerickson,
I have 2 of the 3 <gs id="f5c2356c-9c8b-4140-acd3-998054309316" ginger_software_uiphraseguid="ca3e98db-b392-4c8f-abb2-9830effd21f3" class="GINGER_SOFTWARE_mark">textboxes</gs> functioning exactly how they should, but the textbox1 is the thorn in my side that I can't seem to define properly - I think the issue I'm running into involves the listbox1 being a dynamic list of data dependant on what text is entered in the combobox1 (see image HERE). I have the <gs id="758bfb76-77c3-4970-9a63-e03a74932e4b" ginger_software_uiphraseguid="fd51c92c-1fe6-41ef-8e48-fb7558f9cd27" class="GINGER_SOFTWARE_mark">vlookup</gs> functioning well, but I can't seem to define the <gs id="51a72865-5ab1-486e-ab2d-917af24cf50b" ginger_software_uiphraseguid="fd51c92c-1fe6-41ef-8e48-fb7558f9cd27" class="GINGER_SOFTWARE_mark">currname</gs> variable to match the listbox1 selected value. The code below is functioning properly when <gs id="36be1e44-feb9-4d33-b2b9-d19c55d2aae6" ginger_software_uiphraseguid="300134c5-8bda-475e-8082-e79b19528aa4" class="GINGER_SOFTWARE_mark">currname</gs> is defined as a fixed text (result shown HERE), but I need it to be defined as the listbox1 selected value. I've tried too many <gs id="b3ec2ddd-eb49-4950-b952-8626524e023e" ginger_software_uiphraseguid="6e772986-b502-4473-adac-909b4722e0d1" class="GINGER_SOFTWARE_mark">varia</gs>tions to list on this Any thoughts on how <gs id="c48b972b-047c-452b-9f9f-2b311a0356c1" ginger_software_uiphraseguid="5cb42807-0bda-40ba-ba1c-af3f2d1c269c" class="GINGER_SOFTWARE_mark">to do</gs> this? If you need to see <gs id="62e49f0a-d714-4260-afdf-3cd8bf255919" ginger_software_uiphraseguid="050a4410-951f-42b7-a149-0784d4393991" class="GINGER_SOFTWARE_mark">additional code</gs> from the <gs id="0f1153c0-a96a-499f-8915-3b484439bbcd" ginger_software_uiphraseguid="050a4410-951f-42b7-a149-0784d4393991" class="GINGER_SOFTWARE_mark">userform</gs>, let me know and I can message to you.

Private Sub Listbox1_change<gs id="e316673c-8ba7-479c-add7-57f5fd7d5e1e" ginger_software_uiphraseguid="b6d52902-e201-40e7-99d8-0cbe85b2fbae" class="GINGER_SOFTWARE_mark">(</gs>)


Dim ws As Worksheet: Set <gs id="6667299f-9855-46d4-b79e-8379e2046d3a" ginger_software_uiphraseguid="22da6019-f7ce-409e-b0b9-074097a265af" class="GINGER_SOFTWARE_mark">ws</gs> = Sheets<gs id="a1727dbb-7f7e-46a3-8212-a2d68e39f8d5" ginger_software_uiphraseguid="22da6019-f7ce-409e-b0b9-074097a265af" class="GINGER_SOFTWARE_mark">(</gs>"Hierarchy Codes")
Dim <gs id="0752d998-50e5-42f9-bcdb-51d1cf4c809d" ginger_software_uiphraseguid="873ccd2f-564c-4ed6-b91d-a6fb751222d8" class="GINGER_SOFTWARE_mark">rngLook</gs> As Range: Set <gs id="02106617-021e-430c-ab3f-4db342f5fec0" ginger_software_uiphraseguid="873ccd2f-564c-4ed6-b91d-a6fb751222d8" class="GINGER_SOFTWARE_mark">rngLook</gs> = <gs id="46481fa2-938b-496e-99ea-2e44a5df6530" ginger_software_uiphraseguid="873ccd2f-564c-4ed6-b91d-a6fb751222d8" class="GINGER_SOFTWARE_mark">ws</gs><gs id="23806e41-d57f-427a-8fc5-28ba9f691245" ginger_software_uiphraseguid="873ccd2f-564c-4ed6-b91d-a6fb751222d8" class="GINGER_SOFTWARE_mark">.</gs>Range<gs id="09e0b0c8-4281-4fec-a8c5-b85667678b84" ginger_software_uiphraseguid="873ccd2f-564c-4ed6-b91d-a6fb751222d8" class="GINGER_SOFTWARE_mark">(</gs>"$B<gs id="37a212eb-0ed9-4e03-bca6-cfa0665f2440" ginger_software_uiphraseguid="873ccd2f-564c-4ed6-b91d-a6fb751222d8" class="GINGER_SOFTWARE_mark">:</gs>$C")
Dim <gs id="13d3e5c3-7460-4c03-9f6a-0acbbe408545" ginger_software_uiphraseguid="8d9ac33b-673b-49f8-8ebd-89054145070c" class="GINGER_SOFTWARE_mark">currname</gs> As String
Dim <gs id="11da0251-30b6-4f17-9bc9-09778c86776b" ginger_software_uiphraseguid="ea564214-af12-4915-8403-7bce361d5608" class="GINGER_SOFTWARE_mark">HSales</gs> As Variant
Dim wss As Worksheet: Set <gs id="42d6a261-6c5a-420d-9aed-8a67c2efcf3f" ginger_software_uiphraseguid="a943fd50-0117-4fd8-8b6e-816c8c0b1329" class="GINGER_SOFTWARE_mark">wss</gs> = Sheets<gs id="8153510f-4abe-428b-982c-cfa303336980" ginger_software_uiphraseguid="a943fd50-0117-4fd8-8b6e-816c8c0b1329" class="GINGER_SOFTWARE_mark">(</gs>"Account Discounts Raw")
Dim TotSales As String
<gs id="26b841b5-aeb3-4e16-a3a8-27bfbc36f034" ginger_software_uiphraseguid="38dea7c2-0abe-4d42-bbd9-3889eb02ba1e" class="GINGER_SOFTWARE_mark">TotSales</gs> = Application<gs id="12e1da2e-a9cb-4aa3-832c-d6d3573a63f2" ginger_software_uiphraseguid="38dea7c2-0abe-4d42-bbd9-3889eb02ba1e" class="GINGER_SOFTWARE_mark">.</gs>WorksheetFunction<gs id="81368d03-39da-487a-958a-9ba31c6f647c" ginger_software_uiphraseguid="38dea7c2-0abe-4d42-bbd9-3889eb02ba1e" class="GINGER_SOFTWARE_mark">.</gs>Sum<gs id="135e705e-5418-48b0-b7fd-206a4f3e10ce" ginger_software_uiphraseguid="38dea7c2-0abe-4d42-bbd9-3889eb02ba1e" class="GINGER_SOFTWARE_mark">(</gs><gs id="2fcab392-f360-4dfb-95fd-993bcf755fc0" ginger_software_uiphraseguid="38dea7c2-0abe-4d42-bbd9-3889eb02ba1e" class="GINGER_SOFTWARE_mark">wss</gs><gs id="df66274b-01b5-419a-bb0f-fde09d50b16c" ginger_software_uiphraseguid="38dea7c2-0abe-4d42-bbd9-3889eb02ba1e" class="GINGER_SOFTWARE_mark">.</gs>Range<gs id="dca4769d-226b-4f7e-855e-e0712be56789" ginger_software_uiphraseguid="38dea7c2-0abe-4d42-bbd9-3889eb02ba1e" class="GINGER_SOFTWARE_mark">(</gs>"M<gs id="64b17207-cb85-4239-93a5-64aa88d8471f" ginger_software_uiphraseguid="38dea7c2-0abe-4d42-bbd9-3889eb02ba1e" class="GINGER_SOFTWARE_mark">:</gs>M"))


'<gs id="8c4019c3-e3f6-4cbf-9e99-0da2953095ee" ginger_software_uiphraseguid="9511d1a8-1877-47a8-bc54-77e2940e4c96" class="GINGER_SOFTWARE_mark">within</gs> a loop
<gs id="b8f3af10-e1ca-4c64-b9bf-b38f4be1c6be" ginger_software_uiphraseguid="4c011055-5414-47e4-ab6c-f03202910231" class="GINGER_SOFTWARE_mark">currname</gs> = "CSC330 SMALL MOLECULES"    'need to re-define this line as listbox1 selected value
<gs id="d1409bdd-7174-45fc-b139-d71f1ff6660a" ginger_software_uiphraseguid="afdbd039-de1f-4e79-9418-3c5fcac5597d" class="GINGER_SOFTWARE_mark">HSales</gs> = Application<gs id="cfd928d3-bb2b-41ec-9cdf-43850ba48e01" ginger_software_uiphraseguid="afdbd039-de1f-4e79-9418-3c5fcac5597d" class="GINGER_SOFTWARE_mark">.</gs>VLookup<gs id="a1584946-0c44-4a67-9057-dda2131a336e" ginger_software_uiphraseguid="afdbd039-de1f-4e79-9418-3c5fcac5597d" class="GINGER_SOFTWARE_mark">(</gs><gs id="ff6bff4b-ac95-4f79-a366-5e203a717533" ginger_software_uiphraseguid="afdbd039-de1f-4e79-9418-3c5fcac5597d" class="GINGER_SOFTWARE_mark">currname</gs>, <gs id="f2d3ae38-5dfb-46ff-b77b-e6870e19b82d" ginger_software_uiphraseguid="afdbd039-de1f-4e79-9418-3c5fcac5597d" class="GINGER_SOFTWARE_mark">rngLook</gs>, 2, False)


If IsError<gs id="1d897e9c-4059-419c-a89c-d66c5e706b9b" ginger_software_uiphraseguid="f6783501-0325-4b66-84f3-5b0e91ca44ef" class="GINGER_SOFTWARE_mark">(</gs><gs id="d5378e53-5530-4dbc-a6e0-c100aa314403" ginger_software_uiphraseguid="f6783501-0325-4b66-84f3-5b0e91ca44ef" class="GINGER_SOFTWARE_mark">HSales</gs>) Then
 MsgBox "no match"
Else
    TextBox1.Value = <gs id="4908ad82-6003-44b4-a32d-108e144cf314" ginger_software_uiphraseguid="5815902b-75c7-4eca-8ea8-a19787fface7" class="GINGER_SOFTWARE_mark">HSales</gs>
    TextBox1 = Format<gs id="8610a4e4-ba8c-4aa7-96b7-4bb9e8393dbe" ginger_software_uiphraseguid="ef9cad27-d788-49b5-95a4-33a72f811c35" class="GINGER_SOFTWARE_mark">(</gs>TextBox1, "$#<gs id="0b3d6154-963b-44e1-92dd-5a666bb7159c" ginger_software_uiphraseguid="ef9cad27-d788-49b5-95a4-33a72f811c35" class="GINGER_SOFTWARE_mark">,</gs>##0<gs id="aed9cd14-d39b-45ac-b770-dcc1d7c34232" ginger_software_uiphraseguid="ef9cad27-d788-49b5-95a4-33a72f811c35" class="GINGER_SOFTWARE_mark">.</gs>00")
    TextBox2.Value = TotSales
    TextBox2 = Format<gs id="59f1a161-75c2-48be-8a00-efef4949df4a" ginger_software_uiphraseguid="fba49553-e72c-40de-b441-bced94999caa" class="GINGER_SOFTWARE_mark">(</gs>TextBox2, "$#<gs id="0f3aa4e1-548c-4ee3-9502-d901545ce97e" ginger_software_uiphraseguid="fba49553-e72c-40de-b441-bced94999caa" class="GINGER_SOFTWARE_mark">,</gs>##0<gs id="a5c5b27e-9197-434f-abb5-cf1b69f700dd" ginger_software_uiphraseguid="fba49553-e72c-40de-b441-bced94999caa" class="GINGER_SOFTWARE_mark">.</gs>00")
    TextBox3.Value = TextBox1.Value / TextBox2.Value
    TextBox3 = Format<gs id="00602c0b-d506-46cd-b49e-16c23efce847" ginger_software_uiphraseguid="10660923-09fc-4be0-a0cc-560160636f23" class="GINGER_SOFTWARE_mark">(</gs>TextBox3, "0.00%")
End If
End Sub

Thanks so much,
Jonathan