A Little Polish

In the last post we cleaned up what the macro recorder generated.  Today we fix one more user interface (UI) problem.  Today we will replace the generic “Enter Parameter Value” with a more meaningful input box.  Here is what it will look like:

Dim s As String
   
s = Trim( _
         InputBox("Enter State Code:" & vbCr & vbCr & _
             "'%' is a wildcard.  By itself it will retrieve all states. " & _
             "'N%' will retrieve all states beginning with 'N'" & vbCr & _
             "'%Y' will retrieve all states ending in 'Y'", _
             "State Code Prompt", "%") _
        )      

If s > "" Then

The Trim statement removes any blanks before or after the user’s input.  The InputBox statement creates the form.  If the user hits cancel, “s” will be empty and we shouldn’t do anything.  If “s” is not empty, we must incorporate it in our SQL string.  Add the red colored text into this existing line (from last post).

"WHERE  O.`Customer ID` = C.ID AND C.`State/Province` LIKE '" & s & "'") 

This next bit completes the if statement but it must be just before the End Sub

End If

Congratulations, we just added a little polish to our spreadsheet.