Return a userdefined cell reference from a dialog

 2000-02-05    Dialogs    2    72

If you want a user to respond with a cell reference in your custom dialog by selecting cells in a worksheet, you follow these simple steps:

  1. Insert a dialog sheet in the workbook
  2. Add an editbox for the cell reference
  3. Edit the properties for the editbox by doubleclicking it.
  4. In the properties dialog (Control-sheet) you can apply that the editbox is going to contain a cell reference
  5. Close the dialog.
When the user activates the editbox in your custom dialog he/she will be able to select a cell reference by clicking in the worksheet behind the dialog and select one or more cells. The cell reference will automatically be filled in the editbox.

In Excel97 or later you would use the RefEdit control instead of an EditBox control.


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

OPE | 2009-07-20 15:14:34 (GMT)

If your dialog is a UserForm (supported by Excel 97 or later), you can display it like this:

Sub ShowMyUserForm
' display a userform
Load UserForm1
UserForm1.Show
Unload UserForm1
End Sub


If your dialog is a DialogSheet (used by Excel 5 & 95, also supported by later versions), you can display it like this:

Sub ShowMyUserDialog
' display a dialog sheet
ThisWorkbook.DialogSheets("Dialog1").Show
End Sub


Application.Dialogs(xlDialogConstantNumber) is used to display any of the application's built-in dialogs that are available to the developers, e.g. like this:

Sub ShowApplicationDialog
' display the save as dialog
Application.Dialogs(xlDialogSaveAs).Show
End Sub

Aaron Wolfe | 2009-07-20 14:52:30 (GMT)

Sorry if this is a stupid question, but I am a newbie with Excel VBA.

I was able to do this much. Now how can I run this dialog? Excel calls the dialog box "DialogFrame1" as best I can tell, but "Application.Dialogs(DialogFrame1).show" gives me an error like it does not recognize that name.

Thanks.