Set previously selected item as selected in ListBox

[whitehawk81]

Hi, I haven't posted here for while. But unfortunately I'm facing an issue with a userform listbox selection change, that exceeds my knowledge.
The goal, I would like to achieve is following:
There is a multicolumn single select listbox, where the selection populates some textboxes based on the selected data.
You can select any row and the fields get updated accordingly, but changing a value in a textbox gets detected and if you click to another list item, you will be prompted if you would like to keep the changes or abandon them.
In case you select to keep the changes, the listbox should be reset to the previously selected item.
No matter, which method I try, the selected item in the listbox will not reset to the previous selection, it remains the currently selected.

 

[Akuini]

Maybe something like this:

but changing a value in a textbox gets detected

in the code part that do this, assign the listbox index to a variable. And when you click to another list item & want to keep the changes then use that variable to get to the previously selected item.

 

[whitehawk81]

Maybe something like this:

in the code part that do this, assign the listbox index to a variable. And when you click to another list item & want to keep the changes then use that variable to get to the previously selected item.

Thanks for the suggestion. I stored the listbox index value, but I cannot change the current selection back. Here is my code:

Sub checkLockedSel()
Dim i As Long, lRow As Long, selRow As Long
Dim prompt As String
Dim isModified As Boolean
Dim lockedRow As Variant


lockedRow = Sheet6.Range("Locked_sel").Value
isModified = Sheet6.Range("Sel_modified").Value

If lockedRow = "n/a" Then
    getChangeDetails
Else
    With Me.ticketList

        For lRow = 0 To .ListCount - 1
            If .Selected(lRow) Then
                selRow = lRow
            End If
        Next lRow
        
        If selRow <> lockedRow Then
            
            If isModified = False Then
            
                getChangeDetails
               
            Else
                
                prompt = MsgBox("Some changes haven't been saved yet." & vbNewLine & "Do you really want to abandon the changes?", vbYesNo, "Warning")
            
                If prompt = vbNo Then
                
                    'This is the part, that doesn't seem to work
                    
                    For i = 0 To .ListCount - 1

                        If i = lockedRow Then
                            
                            .Selected(i) = True
                            
                        End If

                    Next i
                    
                Else
                    Sheet6.Range("Sel_modified").Value = False
                    getChangeDetails
                    
                End If
                
            End If
        Else
            getChangeDetails
        End If
    
    End With
End If

End Sub

 

[Akuini]

lockedRow = Sheet6.Range("Locked_sel").Value

1. What is Sheet6.Range("Locked_sel").Value? how does it change its value?
2. How do you populate listbox data to the textbox? via ListBox1 change event?
3. How do you change a value in a textbox? By typing manually or via macro?

It would be better if you can upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.

 

[Akuini]

If you change a value in a textbox by typing manually then try this:

Dim xIndex As Long
Private Sub TextBox1_Enter()
    xIndex = ListBox1.ListIndex
End Sub

Private Sub TextBox2_Enter()
    xIndex = ListBox1.ListIndex
End Sub

''... the same goes to the other textboxes

and apply to your code:

              If prompt = vbNo Then
                    
                    'This is the part, that doesn't seem to work
                    ListBox1.Selected(xIndex) = True
                    
                Else
                    Sheet6.Range("Sel_modified").Value = False
                    getChangeDetails
                    
                End If

 

[whitehawk81]

lockedRow = Sheet6.Range("Locked_sel").Value

1. What is Sheet6.Range("Locked_sel").Value? how does it change its value?
2. How do you populate listbox data to the textbox? via ListBox1 change event?
3. How do you change a value in a textbox? By typing manually or via macro?

It would be better if you can upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.

I uploaded a sample file to Google Drive, here is the link: Listbox-sample.xlsm
To answer your questions:
1. the default value for "Locked_sel" is "n/a", it gets updated in a for loop in Sub getChangeDetails()
2. currently I use the Listbox click event, but I also tried it with the change event
3. currently I only change the date value with the calendar date picker, that changes the isModified value to true

 

[Akuini]

Sorry, it's hard for me to understand your code.
And also I don't have the date picker so I can't test it.

When you say it doesn't work do you mean the code never get to this line?

                    'This is the part, that doesn't seem to work
                    
                    For i = 0 To .ListCount - 1

                        If i = lockedRow Then
                            
                            .Selected(i) = True
                            
                        End If

                    Next i

or it gets there but the lockedRow has incorrect value?

 

[whitehawk81]

Sorry, it's hard for me to understand your code.
And also I don't have the date picker so I can't test it.

When you say it doesn't work do you mean the code never get to this line?

                    'This is the part, that doesn't seem to work
                   
                    For i = 0 To .ListCount - 1

                        If i = lockedRow Then
                           
                            .Selected(i) = True
                           
                        End If

                    Next i

or it gets there but the lockedRow has incorrect value?

The code gets executed, but the final selection remains the newly selected, not the one before, which is stored in the "lockedRow". I removed the date picker and updated the code, so if you change the date manually, it gets detected and the prompt is triggered.

 

[Gokhan Aycan]

Since you are using a single select listbox, why loop through listcount to find selected row?

Listbox.listindex will give the index number starting from 0. -1 being nothing selected. Then you can set the listbox selection with .listindex = #. You can use it with offset to translate to the row on sheet. If that doesn't work, you can instead put the listbox change code in another sub and call it from whenever you want it to run (i.e., both from change event and where you revert back to the previous row).

 

[whitehawk81]

Since you are using a single select listbox, why loop through listcount to find selected row?

Listbox.listindex will give the index number starting from 0. -1 being nothing selected. Then you can set the listbox selection with .listindex = #. You can use it with offset to translate to the row on sheet. If that doesn't work, you can instead put the listbox change code in another sub and call it from whenever you want it to run (i.e., both from change event and where you revert back to the previous row).

Thanks for the suggestion, I updated the sample file - removed the for loop and use the listindex to get the selected item, but when I used the change or click events of the listbox, the selection still didn't get updated, although I set the listindex to the "lockedRow" value.
But I managed to solve this issue by calling the sub in the afterupdate event of the listbox.
Thanks for your guidance!