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
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

 

 

  • didemicrosoftcommunity1830's avatar
    didemicrosoftcommunity1830
    Copper Contributor
    Jun 13, 2021
    Dear JMB17:

    Thank you so much for explaining the reasons why this code should
    be written like this. I have done similar in VB6 but the code is practically
    entirely different.

    I inserted your suggested CODE and it works perfectly.

    Thank you so much

    I am now working on the last button which I most probably will
    need help with and that is the SEARCH button.

    I would certainly appreciate your help with that however, if you can't,
    I understand.

    Thank you nonetheless,
    Dwight
    • JMB17's avatar
      JMB17
      Bronze Contributor
      Jun 13, 2021
      Sure, go ahead.
      • didemicrosoftcommunity1830's avatar
        didemicrosoftcommunity1830
        Copper Contributor
        Jul 07, 2021

        JMB17

         

        Hello JMB17:

         

        Sorry for my delay.

         

        I have included my project in Excel for data entry with provided database of fake

        addresses provided in a Youtube tutorial by Paul DJ Oamen. The delete function you

        were able to fix for me. The search function seams to work however, the search criteria

        is case sensitive but it does find the data.

         

        Nonetheless, what I would like to do is when a particular search is found, that the criteria

        found is highlighted either in the textbox it corresponds to or the row or entire row  in the

        Data list box view.

         

        I  am not an expert programmer in Excel (VBA) or other platforms. I have been programming

        for years in Visual Basic 6.0 though and I hope to learn some of the new VB.NET algorithms

        and syntax available (which as you know, is entirely different from VB6).

         

        But I do like the ability to do the same in Excel because of its simplicity and you don't really have

        to connect to any database using OLE, ODBC or other. The data is right there on the Excel sheet.

         

        And as I am sure you know, many users prefer using Excel as well.

         

        So with that being said, any help with the above will be greatly appreciated and if you incur a cost

        for this, please let me know.

         

        The links for this particular tutorial follow:

         

        Regular:

        https://www.youtube.com/watch?v=HjgbLXlc2tQ

         

        Advanced;

        https://www.youtube.com/watch?v=crVzhWMzfOg

         

        Thank you for your help in this JMB17.

         

        Sincerely,

        Deee

         

         

         

         

Resources