Excel VBA data entry form 2016

[didemicrosoftcommunity1830]

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

Everything is working just fine save for the DELETE button code which

I am not sure why it is not working. It deletes the rows however,

it does not delete the correct corresponding row in the spreadsheet

(worksheet) itself.

Here is the code I am using:

Private Sub btnDelete_Click()
Dim i As Integer
For i = 0 To Range("A65356").End(xlUp).Row - 1
If lstDisplay.Selected(i) Then
Rows(i).Select
Selection.Delete
End If
Next i
End Sub

The intellisense does not work in ("Rows(1).Select and

neither does it work on the next line (Selection.Delete).

Any help to get my DELETE button to work will be greatly

appreciated.  Again, it deletes the row I select in the listbox

(lstDisplay) but does not delete the same row in the worksheet,

Thank you much in advance to all.

Deee

[#2 · Accepted solution]

@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

[#3]

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

[#4]

Sure, go ahead.

[#5]

@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

[#6]

Just wanted to add, I am using Excel 2016! Thank you.

[#7]

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