Excel Built-in Dialog VBA

Excel Built-in Dialog VBA

Dialog

Can be called from VBA. There are many dialogs available in Excel like set Font, Define Names, Cell Formatting, Custom Formula, Conditional Formatting, File Open, File Save As an so on. The dialog object exposes Show() method which helps launching the dialog UI.

The Microsoft Excel Visual Basic object library includes built-in constants for many of the built-in dialog boxes. Each constant is formed from the prefix “xlDialog” followed by the name of the dialog box. For example, the Apply Names dialog box constant is xlDialogApplyNames, and the Find File dialog box constant is xlDialogFindFile. These constants are members of the XlBuiltinDialog enumerated type.

Activate Dialog: Allows user to activate an object.

SaveAs Dialog: Allows user to Save a new file.

Format Cell Dialog: Allows user to format a cell in sheet.

Excel offers 262 dialogs under dialogs collection which can be accessed using VBA. I shorten down the list below but you can download all constants from here :

  • xlDialogActivate
  • xlDialogActiveCellFont
  • xlDialogAddChartAutoformat
  • xlDialogAddinManager
  • xlDialogAlignment
  • xlDialogApplyNames
  • xlDialogApplyStyle
  • xlDialogAppMove
  • xlDialogAppSize
  • xlDialogArrangeAll
  • xlDialogAssignToObject

Show() method

Public Sub ShowSaveAsDialog()
	Application.Dialogs(xlDialogSaveAs).Show
End Sub

Count property

Public Sub DialogsCount()
	MsgBox Application.Dialogs.Count
End Sub

Validate Result

Public Sub ShowSaveAsDialog()
    Dim dlgResult
    dlgResult = Application.Dialogs(xlDialogOpen).Show
	'Validate Result
	If dlgResult="False" Then
		MsgBox "You cancelled Dialog"
	End If
End Sub

Note: if user cancels the dialog, the result would be captured as False and must be validated as string as given in the above example else user gets a valid file name with full path.

In Line Example: The following code example opens an e-mail message in Microsoft Outlook with the current workbook attached.

Sub SendEmailMail()
    Application.Dialogs(xlDialogSendMail).Show arg1:="abc@mymail.com", arg2:="This goes in the subject line"
End Sub

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Next>>Some Useful Functions VBA

Leave a Reply

Your email address will not be published.