How to reset the ListIndex property within a ListBox Change Event

[ttt123]

Hello,

suppose you have a form with a ListBox on it and someone selects a different ListIndex, I want to have a MsgBox asking if they are sure they want to make the change; if Yes, then go ahead and change the ListIndex, if no, then reset back to the original ListIndex.

The problem is that when vbNo is the response, my code seems to trigger another Change event after the original call to ListBox1_Change() and the Index never gets reset.

Does anyone know how to get around this?

You can paste my example code in a userform with a listbox control to get it to work.

Thanks in advance for your help.
Cheers,
Taylour

Private bEnableEvents As Boolean
Private nPreviousListIndex As Long

Private Sub ListBox1_Change()
    If Not bEnableEvents Then Exit Sub
    
    Dim nResponse As Long
    nResponse = MsgBox("Are You Sure?", vbYesNo)
    If nResponse = vbYes Then
        nPreviousListIndex = Me.ListBox1.ListIndex
    Else
        bEnableEvents = False
        Me.ListBox1.ListIndex = nPreviousListIndex
        bEnableEvents = True
    End If
    
End Sub

Private Sub UserForm_Initialize()
    Me.ListBox1.AddItem "1"
    Me.ListBox1.AddItem "2"
    Me.ListBox1.AddItem "3"
    Me.ListBox1.ListIndex = 1
    nPreviousListIndex = Me.ListBox1.ListIndex
    bEnableEvents = True
End Sub

 

[wigi]

Did you check out Chip's page on the subject?

http://www.cpearson.com/excel/SuppressChangeInForms.htm

 

[Rick Rothstein]

[Untested] Instead of you trying to enable/disable the change event using your own global flag variables, use the built-in facilities to do it. Change your ListBox1_Change event code to this...

Private Sub ListBox1_Change()
    Dim nResponse As Long
    nResponse = MsgBox("Are You Sure?", vbYesNo)
    If nResponse = vbYes Then
        nPreviousListIndex = Me.ListBox1.ListIndex
    Else
        Application.EnableEvents = False
        Me.ListBox1.ListIndex = nPreviousListIndex
        Application.EnableEvents = True
    End If
End Sub

Note that I also removed the global declaration for your bEnableEvents variable since it is no longer being used.

 

[wigi]

Rick,

Application.EnableEvent does not work on a userform. That's why I referred to the article, where a workaround is provided.

 

[Kenneth Hobson]

Private bEnableEvents As Boolean
Private nPreviousListIndex As Long
'http://www.cpearson.com/excel/SuppressChangeInForms.htm
Public EnableEvents As Boolean


Private Sub ListBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Me.EnableEvents = False Then GoTo EndSub
    
    Dim nResponse As Long
    nResponse = MsgBox("Are You Sure?", vbYesNo)
    If nResponse = vbYes Then
        nPreviousListIndex = Me.ListBox1.ListIndex
    Else
        bEnableEvents = False
        Me.ListBox1.ListIndex = nPreviousListIndex
        bEnableEvents = True
    End If
EndSub:
    Me.EnableEvents = True
End Sub

Private Sub UserForm_Initialize()
    Me.ListBox1.AddItem "1"
    Me.ListBox1.AddItem "2"
    Me.ListBox1.AddItem "3"
    Me.EnableEvents = False
    Me.ListBox1.ListIndex = 1
    nPreviousListIndex = Me.ListBox1.ListIndex
End Sub

 

[Rick Rothstein]

Application.EnableEvent does not work on a userform. That's why I referred to the article, where a workaround is provided.

I know that, however, silly me....
I completely read over the UserForm part.

 

[wigi]

Hi,

The BeforeUpdate event is a good one. Here's the code how I would use it (I prefer the Tag property over a variable).

Public EnableEvents As Boolean

Private Sub UserForm_Initialize()
    With Me.ListBox1
        .List = Array(1, 2, 3)
        .ListIndex = 0
        .Tag = .ListIndex
    End With
End Sub

Private Sub ListBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Not EnableEvents Then EnableEvents = True: Exit Sub

    With Me.ListBox1
        If MsgBox("Are You Sure?", vbYesNo) = vbYes Then
            .Tag = .ListIndex
        Else
            EnableEvents = False
            .ListIndex = .Tag
            EnableEvents = True
        End If
    End With

End Sub

 

[ttt123]

thanks guys, your methods with BeforeUpdate() work great.

Cheers,
Taylour