Homework
1. Create a way to remove an entry
If you make a mistake and you need to correct it, you don’t want to manually do it. You want to be able to click a button and have it done for you. You already have a combo box with the names of everything in the list. It seems to me you only need another button and a subroutine to carry out the work. You probably want to completely eliminate the line and have the others automatically moved up. I will give you one line of code, with that and all the code discussed here, you should be able to create an addition to this project that will remove a line at the click of a button.
Your helper code is:
Rows(rowCounter).EntireRow.Delete
For more information on removing rows in Excel using VBA, visit the Delete Entire Rows and Columns page.
2. Improve the URL Validation
If you can prevent having to remove a line by helping to ensure that your URLs are valid when you enter them, that would be even better. There are several ways you can go about this, I will lead you with a hint on the easiest I can think of, using InStr().
Back in part 2, we created a simple check to look for blanks. That line is below:
If DisplayName = "" Or UrlLocation = "" Then
The InStr() function is a prebuilt function that returns a boolean value – True or False. You give it a string, you tell it what you are looking for in that string. It either finds it (True) or it doesn’t (False).
For example:
InStr("Hello World", "Hello")
The above code would return True. It is checking the string “Hello World” for the existence of the string “Hello” and since it finds it, True is the boolean value it will return
InStr("Hello World", "My String")
The above code would return False. “My String” is not contained inside of “Hello World” and as a result, False is the boolean value it will return.
Using ‘InStr(UrlLocation, "https://")'
we could help to make sure that every URL contains https:// before we let it put the URL in the list. We could equally do it with “.com” or “.org” or anything we want to make sure is included in that entry.
For more information on the InStr function, please visit the InStr() Function page.
Categories: Excel, Technology, Tutorials, VBA