Dialog Boxes
Is there any way to force a line break in the text of a message box?
Yes. You can use a carriage return or a line feed character to force a new line. The following statement displays the message box text on two lines.
MsgBox "Hello" & Chr(13) & "There"
Calling a custom dialog box worked fine -- until I converted the XLS file to an XLA add-in. Now, I get an error message whenever I try to access the custom dialog box using the Show method. What's wrong?
You need to qualify the reference to the dialog sheet with a reference to its workbook. This makes the dialog box callable when any workbook is active. Using ThisWorkbook is the most general way to do this. Here's an example:
ThisWorkbook.Dialogsheets("Dialog1").Show
How can you give the controls in a custom dialog box more meaningful names? Names like Option Button 8 and Button 12 are difficult to use.
Use the Name Box at the left side of the input line. This is the same tool used to provide names for cells and ranges. Just select the object, type the name in the Name Box, and press Enter.
I tried the technique described in the previous question, but it doesn't work. I simply cannot create a new name for a dialog box control.
You must press Enter after entering the name. Simply clicking elsewhere does not work.
My custom dialog box has several buttons that all call the same macro. How can I determine which button was clicked?
Use Application.Caller. This returns the name of the control that called the macro.
I know you can link the value of a custom dialog box control to a worksheet cell, but I can't get this to work with some controls. Why?
Not all dialog box controls can be linked to cells. Right-click a control and then select the Control tab of the Format Object dialog box. If Cell Link does not appear as an option, that control cannot be linked to a cell. You'll have to write VBA code to transfer the information to a cell.
I've created a custom dialog box, and the controls are linked to cells on the worksheet. Is this the best way to do this?
No. In general you should not link dialog box controls to worksheet cells unless you absolutely must. Doing so can slow your application down since the worksheet is recalculated every time a control changes the cell.
Some of my applications that use custom dialog boxes generate a error that tells me I'm out of stack space. What gives?
This error may occur if you try to display more than two dialog boxes at a time. Generally, you should close a dialog box before displaying another one. SQL queries also use this stack space, so it's often not possible to execute an SQL query while a dialog box is displayed.
I need to display a progress indicator (like those used during software installation) while a lengthy process is being executed. How can I do this?
Unfortunately, there is no easy and reliable way to do this. You can use the status bar or Excel's title bar to display progress reports (but these aren't very noticeable). Another option is to display the progress information in a small workbook window (but you have to make sure this window is always visible).
I have several 1-2-3 for Windows files and Quattro Pro for Windows files that contain custom dialog boxes. Is there a utility to convert these to Excel dialog boxes?
No.
I need to concatenate strings and display them in a list box. But when I do so, they aren't aligned properly. How can I get them to display equal spacing between strings?
Unfortunately, you can't. The font used to display text in a dialog box uses proportional spacing - which makes it impossible to line up text. Even using Chr(9), the tab character, doesn't work.
Is it possible to display a built-in Excel dialog box from VBA?
Most (but not all) of Excel's dialog boxes can be displayed using the Application.Dialogs method. For example, the following statement displays the dialog box that lets you format numbers in cells.
Application.Dialogs(xlDialogFormatNumber).Show
Use the Object Browser to display a list of the constants for the built-in dialog boxes (press F2 while in a VBA module, or select the View Object Browser command). Then select the Excel library and then the Constants object. The Method/Properties list will display the constants for the built-in dialogs (they all begin with xlDialog).
I tried the technique in the previous question and received an error message. Why is that?
The Dialogs method will fail if the context isn't appropriate. For example, if you attempt to display the Number Format dialog box when a VBA module is active or when a range is not selected, you will get an error message.
How can I change the font or alignment of text in a label object in a custom dialog box?
You can't. But there are two good alternatives.
One approach is to use a text box in your dialog box. Although text boxes are normally used in worksheets, they work perfectly well in dialog boxes. And you can format them any way you like.
The other alternative is to use a linked picture. Create the text you want to display in a worksheet range and then copy the range to the Clipboard. Then activate your dialog sheet, hold down Shift, and choose the Edit Paste Picture Link command. This creates a picture object that displays the current contents of the linked range - including all of its formatting.
Is it possible to create a custom dialog box without a title bar?
No. The closest you can get is to make the dialog box's caption blank by deleting the text. You can also do this from VBA using a statement such as:
Dialogsheets("Dialog1").DialogFrame.Caption=""
When I double-click an item in a list box on a custom dialog box, the dialog box is dismissed (as if I clicked the OK button). Why does this happen?
In custom dialog boxes, double-clicking an option button or an item in a standard list box activates the default button if one has been set. If you don't want this to happen, turn off the Default attribute for all of the buttons in the dialog box.
Can I create a custom dialog box that displays in the same size, regardless of the video display resolution?
No. To make sure your custom dialog boxes display properly on all video displays, you should create (or test) the dialog boxes using standard VGA mode.
Is it possible to create a custom dialog box that lets the user select a range in a worksheet by pointing - like the method used in the built-in Goal Seek dialog box?
Yes. Add an edit box to your dialog, right-click it and choose Format Object from the shortcut menu. Then select the Reference option to make the edit box accept a cell or range (the user can enter the range reference directly, or point to it in the worksheet using standard methods).
I understand that you can place custom dialog box controls directly on a worksheet. I'm not able to place some of the controls on a worksheet. What's wrong?
Although you can insert most dialog box objects on a worksheet, the following objects cannot be inserted: edit boxes, combination list-edit boxes, and combination drop-down edit boxes.
Custom dialog boxes always appear in the position where the last dialog box appeared. Is there a way to change the position of a custom dialog box?
Not really. However, you can use a rather complicated Windows API call to perform this action.
How can I add another dialog box to a dialog sheet?
You can't. Each dialog sheet can hold only one dialog box.
When I try to copy a cell or range to a custom dialog box, I get an error message stating that locked cells cannot be changed. What does this mean?
This is an erroneous error message. To copy a cell or range to a dialog sheet, use the Edit Paste Picture command or the Edit Paste Picture Link command (you must hold down Shift to access these menu commands).
I'm not able to print anything when a custom dialog box is displayed. Is this right?
I'm not sure if it's right, but that's the way it is. You must dismiss your dialog box before using the PrintOut or PrintPreview method. One approach is to assign the Print button on your dialog to a macro that sets a Boolean variable (for example, PrintIt= True). When the dialog has been dismissed, check the value of the Boolean variable to determine if the user clicked the Print button.
Is there a way to change the grid size in a custom dialog box?
No. If you need to position a control precisely turn the grid off.