Search code examples
excelvbalistboxuserform

Multiple ListBox Selection, dynamically update text based on selections

I have a UserForm which populates a multi-selection ListBox (based on a comma-delimited cell from a hidden sheet).

If a user selects a SINGLE item from the listbox, I want a specific note for that item to be displayed based on their selection.

singleOK

If they select MULTIPLE, I would like the note to ADD the note for the item they just selected to the EXISTING one, without duplicating the originally selected item.

current results

If a user de-selects the original item but keeps the second item, there is now invisible lines that need to be removed. In addition to the duplication...

blank lines

Currently my code is:

'check for nothing selected
If Driver_Sel = "" Then
    'nothing selected
    lbl_driverID.Caption = ""
End If

If InStr(1, Driver_Sel, "PC-CR") <> 0 Then
    'PCCR format
    lbl_driverID.Visible = True
    lbl_driverID.Caption = lbl_driverID.Caption & vbNewLine & "-PC-CR: use Format: PCCR-0000xxxx/001"
ElseIf Not InStr(1, Driver_Sel, "PC-CR") <> 0 Then
    'remove PCCR
    lbl_driverID.Visible = True
    lbl_driverID.Caption = Replace(lbl_driverID.Caption, "-PC-CR: use Format: PCCR-0000xxxx/001", "")
End If

If InStr(1, Driver_Sel, "PRTS") <> 0 Then
    'PRTS
    lbl_driverID.Visible = True
    lbl_driverID.Caption = lbl_driverID.Caption & vbNewLine & "-PRTS: include a PRTS#"
ElseIf Not InStr(1, Driver_Sel, "PRTS") <> 0 Then
    'remove PCCR
    lbl_driverID.Visible = True
    lbl_driverID.Caption = Replace(lbl_driverID.Caption, "-PRTS: include a PRTS#", "")
End If

I think this is because I have the caption for the label (blue text) use it's existing value and then add the new item.. even if it is duplicated.

I'm a bit stumped here... any help on only show one instance of the note?

Thank you in advance!!

Solution

  • lbl_driverID.Caption should be reset between executions. Otherwise, it just accumulates text every time the list box selection changes.

    Try lbl_driverID.Caption = "" at the beginning of your If Else block.

    For example:

    The user opens a new user form. The user selects "PC-CR" as the first value. lbl_driverID.Caption is blank and the script assigns it the value "-PC-CR: use Format: PCCR-0000xxxx/001".

    Then the user selects something else, but the selection still contains the first selected value "PC-CR". So the script re-adds the same value to the caption. This is where the double-up is coming from.

    For the invisible lines:

    That is because you are adding vbNewLine to the caption for each new item but you are only removing the text and not the vbNewline. See the difference between these two lines:

    lbl_driverID.Caption & vbNewLine & "-PC-CR: use Format: PCCR-0000xxxx/001"
Replace(lbl_driverID.Caption, "-PC-CR: use Format: PCCR-0000xxxx/001", "")

    Notice that vbNewLine is not accounted for.