The VBA Application.InputBox provides a dialog for you to get a response from the user.
The Microsoft Visual Basic for Applications window displays your VBA environment in Excel 2016: Next, let's analyze the Properties window in the VBA environment. The Properties window is usually found directly below the Project Explorer in Excel 2016. It displays the properties for the object currently highlighted in the Project Explorer. Create InputBox with InputBox function. VBA code to create InputBox with InputBox function. #2: Create MsgBox with multiple lines (new line or line break) VBA code to create MsgBox with multiple lines (new line or line break) To create a message box with multiple lines (by including a new line or using line breaks) using VBA, use a statement with the following structure. In VB macro window, when you click or enter CTRL + SPACE VBA Intellisense, a drop-down menu appears, which helps you out in writing the code & you can autocomplete words in the VB editor. After typing FORMAT and click on spacebar below-mentioned syntax appears. Syntax of VBA Format Function in Excel. The syntax for VBA Format function in excel is as follows.
You can specify the response type from the user. These include numbers, string, date and a range.
If you want to get a single piece of text or value from the user you can use the InputBox. The following code asks the user for a name and writes the user’s response to the Immediate Window(Ctrl + G to view)
Contents
- 7 InputBox Type Parameter Options
Important
Confusingly there are two InputBoxes in Excel VBA.
- Application.InputBox
- InputBox(also calledVBA.InputBox)
They are almost the same except that:
- Application.InputBox allows you to specify the variable type of result e.g. String, integer, date, range.
- The Application.InputBox parameters Left and Top are not used by VBA.
In, the example below, the Application.InputBox allows you to specify the type but the VBA.InputBox doesn’t:
In this article, we will be dealing primarily with the Application.InputBox.
InputBox Syntax
InputBox prompt, title[optional], default [optional], left[optional], top[optional], helpfile[optional], Left[optional]
InputBox Parameters
prompt – this is the text displayed by the InputBox e.g. “Please enter a number between one and ten”, “Please select a range”.
title[optional] – this is the text that is displayed in the title bar of the InputBox.
default[optional]– this will be the response if no response is entered by the user.
left[optional] – not used. If you need to position the InputBox you need to use the VBA.InputBox.
top[optional] – not used. If you need to position the InputBox you need to use the VBA.InputBox.
helpfile[optional] – specifies a related help file if your application has one(hint: it probably doesn’t unless it is a legacy application.)
helpfilecontextidl[optional] – specifies a position in the help file.
type[optional] – specifies the type of value that will be returned. If this parameter is not used then the return type is text. See below for a list of options for this parameter.
What makes using the InputBox simple is that you really only need to use 4 of these parameters, namely prompt, title, default and type.
VBA Optional Parameters
As, we saw in the above section, VBA has a lot of optional parameters. Sometimes we want to use an optional parameter but don’t need the optional parameters before it. We can deal with this in two ways:
- Leave the other optional parameters blank.
- Use the name of the parameter.
Here are examples of each method:
You can see that naming the parameters is a better idea as it makes the code much more readable and understandable.
InputBox Title Parameter
The Title parameter simply allows you to see the Title of the InputBox dialog. The following examples shows this:
InputBox Default Parameter
The default value is simply the value that will be returned if the user does not enter a value. This value is displayed in the InputBox when it appears.
When the following code runs, the value Apple is displayed in the InputBox when it appears:
InputBox Type Parameter Options
Value | Type |
---|---|
0 | Formula |
1 | Number |
2 | String |
4 | Boolean - True or False |
8 | Range |
16 | An error value like #N/A |
64 | Array of values |
You can create your own constants for the Type parameter if you want your code to be more readable:
You can then use them like this:
Getting the Range
To get a range from the user we set Type to 8.
If we set the return variable to be a range we must use the Set keyword like in this example:
If you leave out the Set keyword you will get the runtime error 91: “object variable or with block not set”.
In VBA we can declare the variable as a variant in VBA. This means that VBA will set the type at runtime:
If we replace the Set keyword with a variant then the InputBox will return an array of values instead of the range object:
Cancelling the Range
One problem with selecting the range is that if the user clicks cancel then VBA gives an error.
There is no nice way around this. We have to turn off errors and then check the return value. We can do it like this:
Related Reading
What’s Next?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars.
Text Box Vba Excel For Mac Download
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
Get the Free eBook
Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.
The Problem.
There are many annoyances when designing a VBA project to run on both Windows and Mac computers. One of the most noticeable is related to the difference in screen resolution between the two platforms. On the Mac, it’s 96 dpi, where each dot represents a pixel. In Windows, screen resolution is 72 dpi (dots per inch) and each dot represents a “point”, while pixels are still 96 per inch. Confusing the issue is that VBA in Windows uses points as the measurement unit for designing UserForms, while VBA on the Mac uses pixels. The result is that without applying any correction, UserForms that come out just right in Windows are only 75% as large on the Mac, making them difficult to read.
For example, here is a UserForm designed in Windows, opened in Windows. The text might seem a bit small to me now that my eyes are getting older, but it’s still perfectly legible.
Here is that same UserForm opened on a Mac. The header text is fine, but the text in the dialog itself is too small to read comfortably.
In the past, I supported separate add-ins, one for Windows and the other for Mac. The dialogs for each were appropriately sized, and I took care of other coding idiosyncrasies in the separate files. But it’s a lot of extra work to lug around two separate versions of every file. Every adjustment you make has to be made twice, and it’s hard to remember what changes you’ve made across large projects.
The Solution: UserForms For Mac And Windows.
I have developed a simple routine that is called from each UserForm’s initialize event, which changes the size, position, and font of each control by a factor of 4/3. The text still may not always be perfect, since you can only specify whole number font sizes, so I suppose I should always round up the font size. I do make sure when I design the form that controls are more than large enough in case the text takes up extra space. Aside from this, it works pretty nicely.
Here is a UserForm designed in Windows, but which uses the resizing routine so it displays legibly on either computer. First, it is shown opened in Windows.
Below it’s shown opened on the Mac. The dialogs are equally legible, and the text came out pretty much the same. Sometimes a large block of text may not wrap the same, so if it really matters, you should hard code your line breaks by typing Ctrl+Return while entering the text.
Note: While Microsoft has substantially improved the VB editor on the Mac, you still can’t work with UserForms on the Mac. You have to build them into your file in Windows and them move the file to the Mac.
The Code.
This calls the routine from the UserForm’s code module:
The
#If Mac Then
and #End If
structure indicates a section of code which is conditionally compiled. When using a Mac, the constant Mac
is True, so the code between #If
and #End If
is compiled and run, so the UserForm is resized. In Windows, this code is ignored, and the UserForm is displayed without rescaling.The global resizing factor is placed in an appropriate place in the declarations section of a regular code module:
Vba Add Text Box
And this routine in a regular code module does the resizing:
Example Workbook.
Text Box Vba Excel For Mac Free
I’ve posted a simple workbook that illustrates this technique. Download it by clicking the icon below.
When you open the workbook, you’ll see a blank worksheet with three buttons.
The first button opens the Windows-only UserForm shown in the first and second figures above, in the description of the problem.
The second button opens the Windows-and-Mac UserForm shown in the third and fourth figures, in the description of the solution.
The third button opens another Windows-and-Mac UserForm which also has a simple mechanism to call the
ResizeUserForm
procedure, and resize the UserForm on the fly. Here is it opened in Windows.Enter a number in the New Form Magnification box (for example 2) and click Apply, and the form is resized accordingly.
Enter 1.333333, the ratio between pixels and points, and this is how it will be resized for the Mac, though it’s too large for Windows.
Enter 0.75, the inverse of the above, and you get a sense for how small it would be on the Mac without rescaling. Yeah, way too small.
Here is the same form opened on a Mac. Note that it’s already resized to 1.333333. Resized nicely.
Let’s see how it looks at 200%. Pretty large.
Text Box Vba Excel For Mac Download
If you enter 1 in the box, you can see how the form would look without resizing.
Text Box Vba Excel For Mac Os
In all of your real projects, you should apply this technique to all of your UserForms. Before deploying them to your users, you should make sure that the UserForms that look fine in Windows rescale properly on a Mac. Occasionally text wraps differently or certain other controls may need adjustments. But this eliminates the need for separate UserForms for Mac and Windows.