Search code examples
excelvbadeselect

Excel VBA - How To Deselect A Previosuly Selected Item In A Listbox

I have a userform (uf1_assess_sched) in my Excel VBA project that has a listbox (uf1_listbox3).

When the user selects a single item in this listbox, a second userform (group_1) is opened allowing the user to enter information specific to the selection she made on the first userform. Should the user wish to abandon further entry on group_1, she can exit by clicking a commandbutton called Exit.

Upon exiting, group_1 is unloaded, and uf1_assess_sched takes the forefront. The idea is to allow the user to select another item from uf1)listbox3. However, the selection she originally made is still selected.

How do I deselect this previously made selection.

I have tried:

With uf1_assess_sched
    .uf1_listbox3.listindex = -1
End With

This is the most relevant I could find in any of my searches.

Following Patrick's suggestion, with my limited understanding of Excel VBA, this is how I interpreted his instructions.

With uf1_assess_sched
    For i = 0 To .uf1_listbox3.ListCount - 1
        If .uf1_listbox3.Selected(i) = True Then
            .uf1_listbox3.Selected(i) = False
        End If
    Next i
End With

This to regrettably didn't work. The code did find the true selection, but the entry remained selected in the listbox and also triggered the uf1_listbox3_Click event.

I hope I am providing feedback in the appropriate way, by simply editing my original post. I'm not sure how to add code to a comment. StackOverflow is a new format for me, so doing my best.

With the latest code so kindly provided by Patrick, I managed to get so far before I encountered an error. I made some adaptations to reflect userform and listbox names. I'm getting a "Method or data member not found. " error with the code in the second userform, group_1.

Private Sub exit1_Click()

Dim ui2 As VbMsgBoxResult
Dim lastrow As Long
Dim i As Long

If ws_vh.Range("E2") > 0 Then 'unsaved info
    Me.Label34.Caption = "    Saving unsaved rental data."
    Me.Label34.BorderColor = RGB(50, 205, 50)
    lastrow = ws_rd.Cells(ws_rd.Rows.Count, "A").End(xlUp).Row
    ws_rd.Range("A3:FZ" & lastrow).Sort key1:=ws_rd.Range("A3"), order1:=xlAscending, Header:=xlNo
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True

    Debug.Print Me.Name, "exit1_Click() called"
    uf1_assess_sched.ListBox3_DeSelect    '<--- Error with ".Listbox3_DeSelect"
    Unload Me

    'Unload group_1
    'End
    Exit Sub
End If

If ws_vh.Range("B2") > 0 Then   'Outstanding rentals?
    ui2 = MsgBox("You still have " & ws_vh.Range("C2") & " rentals with critical missing rental information." & Chr(13) & Chr(13) _
        & "Active (Sports) rentals: " & ws_vh.Range("B3") & Chr(13) & "Passive (Picnics) rentals: " & ws_vh.Range("B4") & Chr(13) & Chr(13) _
        & "Are you sure you wish to exit?", vbInformation + vbYesNo, "OUTSTANDING RENTAL INFORMATION")
    If ui2 = vbYes Then
        If ws_vh.Range("N4") > 0 Then
            Me.Label34.Caption = "    Saving unsaved rental data."
            Me.Label34.BorderColor = RGB(50, 205, 50)
            lastrow = ws_rd.Cells(ws_rd.Rows.Count, "A").End(xlUp).Row
            ws_rd.Range("A3:FZ" & lastrow).Sort key1:=ws_rd.Range("A3"), order1:=xlAscending, Header:=xlNo
            Application.DisplayAlerts = False
            ThisWorkbook.Save
            Application.DisplayAlerts = True
            Workbooks("Sports15c.xlsm").Activate
            mbEvents = False

            Debug.Print Me.Name, "exit1_Click() called"
            uf1_assess_sched.ListBox3_DeSelect    '<--- Error with ".Listbox3_DeSelect"
            Unload Me
            Exit Sub
        Else
            Unload Me
            End
        End If
    Else
        Exit Sub
    End If
 End If

Unload group_1
End
End Sub

I did put the subs ListBox1_DeSelect() and ListBoxDeSelect(oListBox As Object) in a separate module (perhaps that is the problem).

Here is that code ...

Sub ListBox3_DeSelect()
ListBoxDeSelect Me.uf1_listbox3
End Sub

Private Sub ListBoxDeSelect(oListBox As Object)
Dim i As Long
If TypeName(oListBox) <> "ListBox" Then Exit Sub
bSkipEvent = True
With oListBox
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            .Selected(i) = False
        End If
    Next
End With
bSkipEvent = False
End Sub

Here is my most recent code (July 19th) ...

USERFORM 1 - uf1_assess_sched (holds listbox for which which user makes selection)

Private Sub uf1_listbox3_Click()
    If mbEvents Then Exit Sub
    Debug.Print Me.Name, "uf1_listBox3_Click() called"
    If bSkipEvent Then Exit Sub
    With uf1_listbox3
        Debug.Print Me.Name, "uf1_listBox3_Click() ListIndex: " & .ListIndex & " (" & .List(.ListIndex) & ")"
        group_1.Show
        'UserForm2.TextBox1.Value = .List(.ListIndex) ' This won't have effect if UserForm2 is True on ShowModal
    End With
End Sub

USERFORM 2 - group_1 (allows user to enter additional data based on the value selected in userform1. User my opt to abandon by pressing EXIT button (exit1))

Private Sub exit1_Click()

    Dim ui2 As VbMsgBoxResult
    Dim lastrow As Long
    Dim i As Long

    If ws_vh.Range("E2") > 0 Then 'unsaved info
        Me.Label34.Caption = "    Saving unsaved rental data."
        Me.Label34.BorderColor = RGB(50, 205, 50)
        lastrow = ws_rd.Cells(ws_rd.Rows.Count, "A").End(xlUp).Row
        ws_rd.Range("A3:FZ" & lastrow).Sort key1:=ws_rd.Range("A3"), order1:=xlAscending, Header:=xlNo
        Application.DisplayAlerts = False
        ThisWorkbook.Save
        Application.DisplayAlerts = True
        Unload group_1
        'End
        Exit Sub
    End If

    If ws_vh.Range("B2") > 0 Then   'Outstanding rentals?
        ui2 = MsgBox("You still have " & ws_vh.Range("C2") & " rentals with critical missing rental information." & Chr(13) & Chr(13) _
            & "Active (Sports) rentals: " & ws_vh.Range("B3") & Chr(13) & "Passive (Picnics) rentals: " & ws_vh.Range("B4") & Chr(13) & Chr(13) _
            & "Are you sure you wish to exit?", vbInformation + vbYesNo, "OUTSTANDING RENTAL INFORMATION")
        If ui2 = vbYes Then
            If ws_vh.Range("N4") > 0 Then
                Me.Label34.Caption = "    Saving unsaved rental data."
                Me.Label34.BorderColor = RGB(50, 205, 50)
                lastrow = ws_rd.Cells(ws_rd.Rows.Count, "A").End(xlUp).Row
                ws_rd.Range("A3:FZ" & lastrow).Sort key1:=ws_rd.Range("A3"), order1:=xlAscending, Header:=xlNo
                Application.DisplayAlerts = False
                ThisWorkbook.Save
                Application.DisplayAlerts = True
                Workbooks("Sports15c.xlsm").Activate

                Debug.Print Me.Name, "EXIT1_Click() called"
                    'UserForm1.ListBox1_DeSelect ' No longer used.
                Set oListBoxToDeselect = uf1_assess_sched.uf1_listbox3 ' [M2] This is required for the DelayedListBoxDeSelect(), if top right [X] is clicked, it won't do DeSelect
                Unload Me
            Else
                Unload Me
                End
            End If
        Else
            Exit Sub
        End If
     End If
            'If ws_vh.Range("N4") > 0 Then
            '    MsgBox "Unsaved rental data. Saving."
            '    lastrow = ws_rd.Cells(ws_rd.Rows.Count, "A").End(xlUp).row
            '    ws_rd.Range("A3:FZ" & lastrow).Sort key1:=ws_rd.Range("A3"), order1:=xlAscending, Header:=xlNo
            '    Application.DisplayAlerts = False
            '    ThisWorkbook.Save
            '    Application.DisplayAlerts = True
            '    Unload Me
            'Else
            '    Worksheets("DYNAMIC").Activate
            '    Unload Me
            'End If
        'End If
    Unload group_1
    'Worksheets("DYNAMIC").Activate
    End

End Sub

For the purpose of testing assume ws_vh.Range("B2") > 0

And the independent helper module ...

Option Explicit

' Generic ListBox Deselector
Sub ListBoxDeSelect(oListBox As Object)
    Dim i As Long
    If TypeName(oListBox) <> "ListBox" Then Exit Sub
    bSkipEvent = True
    With oListBox
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                .Selected(i) = False
            End If
        Next
    End With
    bSkipEvent = False
End Sub

' METHOD 2 [M2] - When UserForm's ShowModal = True
Sub DelayedListBoxDeSelect()
    Dim i As Long
    If TypeName(oListBoxToDeselect) <> "ListBox" Then Exit Sub
    bSkipEvent = True
    With oListBoxToDeselect
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                .Selected(i) = False
            End If
        Next
    End With
    bSkipEvent = False
    Set oListBoxToDeselect = Nothing
End Sub

group_1 userform terminate code

Private Sub UserForm_Terminate()
    Debug.Print Me.Name, "UserForm_Terminate() called"
    Set oListBoxToDeselect = uf1_assess_sched.uf1_listbox3 ' [M2] This is required for the DelayedListBoxDeSelect(), if top right [X] is clicked, it won't do DeSelect
    Application.OnTime Now + TimeSerial(0, 0, 1), "DelayedListBoxDeSelect" ' [M2] Sechedules the Sub named "DelayedListBoxDeSelect" to execute in 1 second.
End Sub

PART 2 - An alternate scenario requiring selection to be deselected.

If i = 0 Then
        MsgBox "Nothing to eliminate."
    '--- > Deselect the user selection in uf_assess_sched.uf1_listbox2 < ---
        Exit Sub
End If
Solution

  • There are at least one way to deal with unintentional UserForm Controls Events.

    As I don't know how your UserForms interact with each other, the simplest I believe is to add a Global Boolean variable to allow you skip the event when required, demonstrated below.

    Edited Tip: I have taken out the Global Boolean variable bSkipEvent and Sub ListBoxDeSelect() out to a Normal Module as a code reduction, and Calls to throw in things like UserForm1.ListBox1. (Ensure the ListBox in that UserForm is Shown and Enabled, otherwise add in Error trapping code).

    When UserForms ShowModal = True (TSM), a different approach is required - Onw way is to schedule a Sub to be called to alter another TSM. Here I used Application.OnTime to schedule DelayedListBoxDeSelect with 1 second right before UserForm2 is completely closed. Note the extra Public object in UserFormHelper. Hope you understand what I am doing here.

    Consider these 2 simple UserForms:
    UserForm1_Design   UserForm2_Design

    1. After loading UserForm1 and Clicked one of the ListBox options:
      UserForm1_ListBox_Click
    2. Clicking on the Command Button on UserForm2 brings back focus to UserForm1:
      Note how the dotted selection in the ListBox1, I think it's good to leave it like that as a reminder of what was selected previously.
      UserForm2_Cmd_Clicked

    Codes:
    UserForm1

    Private Sub UserForm_Initialize()
    bSkipEvent = False
End Sub

Private Sub ListBox1_Click()
    Debug.Print Me.Name, "ListBox1_Click() called"
    If bSkipEvent Then Exit Sub
    With ListBox1
        Debug.Print Me.Name, "ListBox1_Click() ListIndex: " & .ListIndex & " (" & .List(.ListIndex) & ")"
        UserForm2.Show
        UserForm2.TextBox1.Value = .List(.ListIndex) ' This won't have effect if UserForm2 is True on ShowModal
    End With
End Sub

'Sub ListBox1_DeSelect() ' No longer used
'    ListBoxDeSelect Me.ListBox1
'End Sub

    UserFormsHelper (Normal Module)

    Public bSkipEvent As Boolean ' This makes accessible to Userforms and other Modules
Public oListBoxToDeselect As Object '[M2] This is for delayed ListBox Deselect method

' Generic ListBox Deselector
Sub ListBoxDeSelect(oListBox As Object)
    Dim i As Long
    If TypeName(oListBox) <> "ListBox" Then Exit Sub
    bSkipEvent = True
    With oListBox
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                .Selected(i) = False
            End If
        Next
    End With
    bSkipEvent = False
End Sub

' METHOD 2 [M2] - When UserForm's ShowModal = True
Sub DelayedListBoxDeSelect()
    Dim i As Long
    If TypeName(oListBoxToDeselect) <> "ListBox" Then Exit Sub
    bSkipEvent = True
    With oListBoxToDeselect
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                .Selected(i) = False
            End If
        Next
    End With
    bSkipEvent = False
    Set oListBoxToDeselect = Nothing
End Sub

    UserForm2

    Private Sub CommandButton1_Click()
    Debug.Print Me.Name, "CommandButton1_Click() called"
    'UserForm1.ListBox1_DeSelect ' No longer used.
    Set oListBoxToDeselect = UserForm1.ListBox1 ' [M2] This is required for the DelayedListBoxDeSelect(), if top right [X] is clicked, it won't do DeSelect
    Unload Me
End Sub

Private Sub UserForm_Terminate()
    Debug.Print Me.Name, "UserForm_Terminate() called"
    Application.OnTime Now + TimeSerial(0, 0, 1), "DelayedListBoxDeSelect" ' [M2] Sechedules the Sub named "DelayedListBoxDeSelect" to execute in 1 second.
End Sub

    Debug Output
    DebugOutput