CODEWITHSUNDEEP
excelvba
Vincenzo
Vincenzo

Reputation: 321

Listbox_Click run command before selecting new entry

Is it possible to run a command inbetween the moment a user clicks on a listbox item (in a userform) and the item being selected?

I have a mask with a listbox as index and want to make it possible when a user goes to another item, the entries are automatically saved. At the moment I am doing it with a button but I want to prevent the situation that a user goes to a different item and loses all the information put in because he forgot to press the button.

my code if you click on the listbox:

Private Sub ListBox1_Click()
   Dim lrow As Long        
     Values_delete  'Sub that clears all text boxes          
     If ListBox1.ListIndex >= 0 Then    
         lrow = 2 
         Do While Trim(CStr(Tabelle10.Cells(lrow, 1).Value)) <> ""              
             If ListBox1.Text = Trim(CStr(Tabelle10.Cells(lrow, 1).Value)) Then            
                 Values_read (lrow) 'values get read from the excel cells and written into the text boxes            
                 Exit Do            
             End If        
             lrow = lrow + 1
         Loop
     End If
End Sub

As well as my code for the save button:

Public Sub SaveButton_Click()
           Dim lrow As Long
           If ListBox1.ListIndex = -1 Then Exit Sub          
           If Trim(CStr(Abteilung.Text)) = "" Then               
               MsgBox "Error", vbCritical + vbOKOnly, "Error"
               Exit Sub
           End If
           lrow = 2 
           Do While Trim(CStr(Tabelle10.Cells(lrow, 1).Value)) <> ""
               If ListBox1.Text = Trim(CStr(Tabelle10.Cells(lrow, 1).Value)) Then
                   Values_write (lrow)  'Values get written into the cells               
                   If ListBox1.Text <> Trim(CStr(Abteilung.Text)) Then
                       Call UserForm_Initialize
                       If ListBox1.ListCount > 0 Then ListBox1.ListIndex = 0
                   End If                  
                   Exit Do                  
               End If          
               lrow = lrow + 1 
           Loop
    End Sub

Is this even possible?

Upvotes: 1

Views: 374

Answers (2)

Samuel Everson
Samuel Everson

Reputation: 2102

I'm assuming you are using either an ActiveX control on your sheet OR a UserForm control so;

You can use the MouseDown event; below will print the text of the previously selected item (based on the user clicking a new item)

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Debug.Print ListBox1.Text

End Sub

MS Documentation breifly mentions this event: See here

Upvotes: 1

AcsErno
AcsErno

Reputation: 1615

Try MouseDown event that fires before updating anything. When you click on a new item, .Selected or .Listindex points to the previous item within Mousedown. Then you can use MouseMove event to process the lately selected item.

Upvotes: 2

Related Questions