|
VBA
for Excel Index and Search Tool (Click
on the buttons below) |
VBA Part 15 of 25: VBA for Excel Code for Message Boxes (MsgBox) and Input BoxesIn VBA for Excel the message box (MsaBox) is the primary tool to interact with the user. You can use it to inform, alert him or ask him to choose a certain path (Yes/No).
The code in VBA for Excel to generate the
following basic message box is:
When you develop a procedure that takes time to run, you end your code with a basic message box telling the user that the procedure has been executed. MsgBox "The
report is ready" Notice that if you want part of your message to be between quotation marks you have to double the quotqtion marks within the message. When a user clicks on a button in the Excel message box it triggers a procedure that will do something important like delete all data. Always start the code with a message box that asks the user if he is certain that he wants the procedure to run. In this case use a "Yes/No" message box. When working with a "Yes/No" message box you will need to collect the answer given by the user. Step 1: You first declare a variable to store the answer "Dim varAnswer as string". Step 2: You store the answer in the variable with the code: "varAnswer = MsgBox("Are you sure that you want to delete all data?", vbYesNo, "Warning")". Notice the parentheses within which you submit the three arguments; the message, the type of message box and its title. Notice also the quotation marks surrounding the message and the title. Step 3: You deal with the answer with an "IF" statement where if the user answers "No" you just exit the procedure with "Exit Sub" Here is the whole procedure. Sub proDangerousProcedure() Dim varAnswer As String varAnswer
= MsgBox("Are you sure that you want to delete all data?",
vbYesNo, "Warning") End Sub When you create a basic message box (first example above), you don't need to use parentheses. If you develop a more complex message box you will see that when you start writing the line of code (example above) varAnswer = MsgBox( right after the parenthesis Excel shows you what it is expecting as arguments:
1-
The prompt is not optional, it is the message that you want your
user to read in the message box and it must be between quotation
marks.
Choose
your message box and enter a comma. The fourth and fift arguments are rarely used, they are optional and you don't need to enter anything else...but the closing parenthesis. As you can see there are many types of message boxes. The ones that are most often used are the vbYesNo, vbOKCancel, vbRetryCancel. You might also want to use a fancier message box like the vbQuestion, the vbInformation, the vbExclamation or the vbCritical as shown below. From the simple "OK" message box to the "Yes/No/Cancel" message box. From the simple message to complex ones using values of cells or variables. Hre how it is done. There are no message box in VBA for Excel that you can show to the user while a procedure runs for a long time. But there is a way around this. Add a worksheet named "Wait" and insert a picture, a wordart or a message in very large font like "Procedure running" and hide the sheet. Step 1: First
you make the sheet that you have created visible and select it (Lines
1 and 2). Add this code to your long procedure. Just replace the line "Here is your long procedure" by your procedure.: Sub proLongProcedure() Sheets("Wait").Visible=True End Sub VBA for Excel Code for Input BoxesInput boxes are used to require a SINGLE value from the user.
The basic code for an input box is as follow. With this procedure whatever value submitted by the user is entered in cell "A1" of a sheet named "Intro". Sub
proInput() You can also use a different header for the input box (like "Year" in this example Sheets("Intro").Range("A1").Value = InputBox("For what year do you need this report?")
Before we go on I must tell you that it is not possible to impose the password character (*) in the input box. To achieve this you must work with a userform and a text box. See how in the section on userforms and controls. If you want to validate the value submitted by the user before proceeding with the procedure you will not store the value directly into a cells but in a variable. The code would then look like this: Sub
proInput() varInput
= InputBox("For what year do you need this report?") End Sub A variable "varInput" is declared Dim varInput As Integer to receive the value submitted by the user. The user enters a value that is transfered into a variable and then to a cell. Note here that the variable is declared as "Integer" so if the user submits a text "string" an error is generated. You can declare the variable as string Dim varInput As String if you are expecting a text or as date Dim varInput As Date if your are asking for a date. With this approach you can use an error handler or some "IF" statement to validate the value submitted by the user before going on. Here is the code to handle errors (canceled or invalid response from the user) and validate the value sumitted: Sub proInput() On
Error GoTo addError If
year(varInput)> 2006 then Do this and This Exit Sub addError: MsgBox "This is not a date." End Sub In this procedure I first declare a variable of the date type Dim varInput As Date so that if the user submits anything else than a date there will be a VBA error to handle. I then tell VBA that if an oerro occur the procedure should jump to the address addError On Error GoTo addError. This means that if the user submits anything else tan a date the procedure will skip all the lines until it get to addError. A message box will then tell the user that what he has submitted is not a date MsgBox "This is not a date.". If what is submitted is indeed a date I use an "IF" statement to verify if the year of the date is greater than 2006. If it is there is another message box Msgbox "Invalid year" followed by an Exit Sub that puts an end to the procedure. If the value submitted is a date and the year is valid the procedure runs completely until it gets to the other Exit Sub just before the addError: ( with the colon at the end). |
VBA
for Excel Index and Search Tool (Click
on the buttons below) |