Problem with AfterUpdate for Listbox

I am creating a small database in an Excel sheet with three columns. In a userform, the user may add new records or change existing ones. The records can be picked from a listbox which shows all values in the first column (the record IDs). The content of the selected record is shown in a textbox and two comboboxes. When another item from the listbox is chosen, I use the AfterUpdate event of the listbox to load the three values. If a user changes these values and clicks on another item of the listbox, the Excel sheets should be updated. However, first the macro should check whether the record ID already exists. I use the AfterUpdate event for this as well and when the record ID is a duplicate, the item selected in the listbox is reverted to the previously selected one, as the user is in that case not allowed to exit the record. However, changing the ListIndex property in the AfterUpdate seems to provoke a second run through the AfterUpdate, although apparently not directly after changing ListIndex (instead after finishing the entire first AfterUpdate) and I can't seem to capture it when I analyse the macro stepwise.

Part of the problem arises because I am always quite confused by the events BeforeUpdate, Change and AfterUpdate. First, I tried to include the check in a BeforeUpdate event of the ListBox, but setting Cancel to True apparently does not revert the user action in the ListBox.

Is there anyone who could assist me with this problem. Any help would be very much appreciated.

Kind regards,

Wouter

can we see your code/sample workbook?

Thank you for your interest! I have simplified the code a little (and translated everything in English). There is just one column now which is reflected in ListBox1 and the selected item from there is shown in TextBox1. The code now only allows editing the existing entries. It generates the same twofold loop by trying to save an existing entry. Amazingly it does not show up when executing it stepwise.

Please see the code here, or if desired, download the file.
[vba]
Dim intPrevValue As Integer 'value of listbox before change

Private Sub UserForm_Initialize()

'filling the listbox with the relevant entries
ReadEntries

'setting the value of listbox
intPrevValue = -1
ListBox1.ListIndex = 0
Listbox1_AfterUpdate

End Sub

Private Sub Listbox1_AfterUpdate()

Dim intButton As VbMsgBoxResult
Dim rngCell As Range
Dim blnExists As Boolean
Dim blnPreserveEntry As Boolean
Dim strIntendedEntry As String

'circumventing this after initialization by setting intprevvalue to -1
If intPrevValue >= 0 Then

If Range("A1").Offset(intPrevValue).Text <> TextBox1.Text Then

'save changes?
intButton = MsgBox("Save changes?", vbYesNo)

If intButton = vbYes Then

'check whether value already exists -> if so blnExists will be true and what the
'user entered in the textbox should be preserved to edit it further
Set rngCell = Range("A1")
Do
If rngCell.Text = TextBox1.Text Then
MsgBox ("Value does not already exist and therefore cannot be changed!")
blnExists = True
blnPreserveEntry = True
Exit Do
End If
Set rngCell = rngCell.Offset(1)
Loop Until rngCell = ""

'entry does not exist yet? -> update Excel sheet, sort it and reread for listbox
'plus: the entry intended to be selected should be searched for in the updated listbox
'it is stored during updating in strIntendedEntry
If blnExists = False Then
strIntendedEntry = ListBox1.Value
Range("A1").Offset(intPrevValue).Formula = TextBox1.Text
Range("A1", Range("A1").End(xlDown)).Sort Key1:=Columns("A"), Order1:=xlAscending
ListBox1.Clear
ReadEntries
ListBox1.Value = strIntendedEntry
End If

End If
End If
End If

'reverting the user action: setting the listbox to the previous value
If blnExists = True Then
ListBox1.ListIndex = intPrevValue
End If

'updating intprevvalue
intPrevValue = ListBox1.ListIndex

'overwrite textbox if blnpreserveentry = false
If blnPreserveEntry = False Then
TextBox1.Value = Range("A1").Offset(intPrevValue).Text
End If

End Sub

Private Sub ReadEntries()

Dim rngCell As Range
Set rngCell = Range("A1")
For Each rngCell In Range("A1", Range("A1").End(xlDown))
ListBox1.AddItem rngCell.Text
Next rngCell
End Sub[/vba]

changing this seems to have fixed it for me:

[VBA]Private Sub ReadEntries()

Dim rngCell As Range
For Each rngCell In Range("A1:A" & Range("A1").End(xlDown).Row)
ListBox1.AddItem rngCell.Text
Next rngCell
End Sub[/VBA]

When I tried this, it did not help me unfortunately. I would also be quite surprised if it actually worked, as it is a part of programming that is not really involved in the loop.

Could you find the error? I have a, b, c, d, e entered in A1:A5. When I for example select c in the Listbox, try to change c in the Textbox in the already existing a, and then select d, it detects that a already exists, and it should revert the Listbox selection from d back to c, so that the Textbox can be modified to a unique value. However, in that case, it goes through AfterUpdate twice.

Is it making sense what I am saying? Thanks again for your effort!