Consulting

Results 1 to 5 of 5

Thread: Problem with AfterUpdate for Listbox

  1. #1

    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

  2. #2
    VBAX Expert CatDaddy's Avatar
    Joined
    Jun 2011
    Posts
    581
    Location
    can we see your code/sample workbook?
    ------------------------------------------------
    Happy Coding my friends

  3. #3
    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]
    Attached Files Attached Files

  4. #4
    VBAX Expert CatDaddy's Avatar
    Joined
    Jun 2011
    Posts
    581
    Location
    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]
    ------------------------------------------------
    Happy Coding my friends

  5. #5
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •