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

     

     

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

     

     

     

     

    • didemicrosoftcommunity1830's avatar
      didemicrosoftcommunity1830
      Copper Contributor
      Jul 07, 2021
      Just wanted to add, I am using Excel 2016! Thank you.
      • 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