Beyond Excel: VBA and Database Manipulation

Oct 14 2009   9:05PM GMT

A Little Polish

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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:

Input boxes are very simple.  Limited.  But simple.  Below is the code.  This bit must be added just after the: Sub Macro1()
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.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: