Forum Discussion

didemicrosoftcommunity1830's avatar
didemicrosoftcommunity1830
Copper Contributor
Jun 11, 2021
Solved

Excel VBA data entry form 2016

Dear forum:   Device: HP laptop computer 16g RAM 500 HD OS: Windows 10 Professional latest edition   I am beginning a simple application for contacts in Excel 2016 using VBA code and its User f...
excel
  • JMB17's avatar
    JMB17
    Jun 12, 2021

    didemicrosoftcommunity1830 

     

    When using a for loop to delete rows, you should work your way from the bottom up. Otherwise, deleting rows will throw off your loop counter. Also, since the listbox  is zero based, I believe you will need to add 1 to delete the intended corresponding row. Be sure to backup your data before testing.

     

     

    Private Sub btnDelete_Click()
Dim i As Long
For i = Range("A65356").End(xlUp).Row - 1 to 0 Step -1
If lstDisplay.Selected(i) Then
Rows(i+1).Delete
End If
Next i
End Sub

     

     

JMB17's avatar
JMB17
Bronze Contributor
Jul 08, 2021

didemicrosoftcommunity1830 

 

I added the search/findnext procedures. 

 

I noticed there were several parts of the other procedures that were duplicating certain functions (like refreshing the listbox, resetting the input textboxes). I took the liberty of putting those processes in common into separate procedures. Also, I set up a public property to set the source data range before showing the userform to avoid hardcoding the range reference within the userform itself (so, if your source worksheet changes, it shouldn't affect the userform) and a public property to change the listbox multiselect property.

 

Test it out - hopefully, it's along the lines of what you're looking for.

 

Edit - see the code in Sheet1's code window for an example to use those properties and display the form.

 

Resources