xlDialogAlignment

Post date: Oct 26, 2012 9:19:31 PM

    1. Why read this:Learn to use VBA's built in dialogs

    2. Learn to use named parameters

    3. Learn to use positional parameters

    4. Learn all parameters associated with the "Alignment" dialog

Background:

I recently needed this dialog in a project and couldn't find documentation for all parameters. Only the first few were documented. The other parameters are still available to us and we can access them using either their position or name. I'm posting this entry to document these parameters for myself and anyone else.

Dialogs:

Anyone who has used Excel for a while has seen this dialog and many others. Almost all of them are available to us through VBA using:

Application.Dialogs(<dialog#>).Show

The "Alignment" dialog's number is xlDialogAlignment which equates to 43. The See "References" for a link to all dialog numbers. So to show this dialog we would use:

Application.Dialogs(xlDialogAlignment).Show

-or-

Application.Dialogs(43).Show

Test this by entering the line above in VBE's Immediate window.

Parameters:

Many of these dialogs have parameters which are shown in the reference link. This dialog has the following parameters in this order:

    1. Horizontal Alignment (1=General, 2=Left, 3=Center, 4=Right, 5=Fill, 6=Justify, 7=Center across, 8=Distribute)

    2. Wrap text (TRUE/FALSE)

    3. Vertical Alignment (1=Top, 2=Center, 3=Bottom, 4=Justify, 5=Distributed)

    4. Stacked Orientation (TRUE/FALSE)

    5. Indent Level (0-?)

    6. Not used

    7. Shrink to fit (TRUE/FALSE)

    8. Merge cells (TRUE/FALSE)

    9. Degrees (0-360)

    10. Text direction (1=Context, 2=Left to right, 3=Right to Left)

We can refer to these parameters by name or position. Their names are Arg1, Arg2, Arg3 and so forth. To start up this dialog with the "Wrap text" check box checked, we could issue this command:

Application.Dialogs(xlDialogAlignment).Show ,TRUE

-or-

Application.Dialogs(xlDialogAlignment).Show Arg2:=TRUE

The first command uses commas to designate which parameter we are supplying a value for. That example indicates there is nothing in the first position and TRUE in the second position which happens to be the "Wrap text" parameter. The second command uses the dialog's parameter name, Arg2. With named parameters we can specify our values in any order we like. Thus, to start the dialog specifying LEFT horizontal alignment, indent once, no wrapping, no shrinking, no merging, TOP vertical alignment, no degrees, and 'normal' text direction we could use:

Application.Dialogs(xlDialogAlignment).Show Arg1:=2, Arg5:=1, Arg2:=FALSE, Arg7:=FALSE, Arg8:=FALSE, Arg3:=1, Arg9:=0, Arg10:=1

Fun Note:

We can also access dialogs using command bars. Try this in the immediate window:

application.CommandBars.FindControl(ID:=855).Execute

Or...

Application.CommandBars("Worksheet Menu Bar").Controls("Format").Controls("Cells...").Execute

References:

Microsoft documentation: http://msdn.microsoft.com/en-us/library/office/bb211087(v=office.12).aspx