Excel: setting default value for Built In Dialog Box by VBA

Introduction

Excel VBA allows us to  show Excel Dialog Box with our own chosen default value. Sometime it is very helpful. My purpose is only to touch on some important and quick methods to utilise this for our benefit. This is now way a complete tutorial or guide or reference for usage of Dialog Box.

An Example

Example: If we press F5 in excel we are shown Excel Built-In Dialog “Go To”. It accepts a range address and on clicking you will reach that cell.

And if you click the “Special” tab at bottom you reach another dialog box “Go To Special”. This is much more useful than simple “Go To”. We can select all cells with formula, or Validation or Constants. But this dialog is not accessible directly (Another way is Home->Find and Select->Go To Special”)

Say you need to select all cells with formula, the below will do the job.

Application.Dialogs(xlDialogSelectSpecial).Show 3

Paste above code in Immediate window (Press Ctrl+G, if the window is not visible in VB Editor) and see how quickly the result can be achieved.

Later you can put the code in macro and call by a shortcut key.

A Brief Idea to Use the Dialog Box

In VBA every Dialog box has a general structure and it is called by same way.

Application.Dialogs(some_Built_In_Dialogbox_Name).Show Arg1, Arg2,……..

Only the dialogbox name changes and the arguments Arg1, Arg2…..

The type and order of arguments is fixed.

If you press F1 at VB Editor and Type “Built-In Dialog Box Argument” at Search Button you will get a available list of Dialogboxes along with their arguments.

Below is a link.

https://msdn.microsoft.com/en-us/library/office/ff838781.aspx?f=255&MSPPError=-2147217396

How to set value

To set value we need to know the mapping of arguments with control and the data type of each argument.

Know your dialog box

First we need to see carefully the dialog box in user interface. It is like any other user form with checkbox, command button, option button etc. Each of this controls needs specific data type of inputs. To set value to these controls we need to know that.

Below is a illustrative list

Type of Control	Input Type
Check Box	TRUE or Any Positive Number will make it "Checked", FALSE or 0 will make it "Uncheked"
List Box	Index of the item which you want to select.
Text Box	Text you want to display.
Range Reference Box	Create Name for the range or use Range("address")
Option Group	Index of the option button in option group
Combo Box	Text you want to display or Index of the item which you want to select.

Map with arguments

As the argument does not clearly show what is it’s data type, we can get that info by relating each argument with actual control it refers to.

But unfortunately the name of argument and name of control is not same. Neither their order is same. But if you read carefully the name of arguments and name of controls, it can be understood with no difficulty.

For Example xlDialogCreateNames has one argument “Top”. By referring to the control you know that it refers to checkbox labelled “Top”. As it is checkbox so it’s data type is Boolean.

Proceed Slowly

Start with 1^st argument, if it is OK then go to 2^nd …..One nice feature is that it does not force you to give all argument. You can specify as long as you wish. The arguments which are not filled by you will be done by excel.

So be confident with 1^st, then 2^nd …….

Some points

1. Some dialog box accepts array of values. For example data consolidate “Reference”. Use VBA Array function for that.
2. In any case you can use variable or range("address") instead of hard coded value.
3. If any argument is range reference and you use a string, it will be taken as range name.
4. If you want to leave one argument then give a comma immediately after previous argument's comma. Like 1stARg,,3rdArg. Here 2nd argument is left and will be set by excel.

Some issue to know about

1. Some Dialog box needs specific condition to be filled. For Example, Page Set Sup needs non-empty sheet  before running. Another such case is Paste Special dialog which will not be available if no copy done.
2. Newly introduced Dialog from Office 2007 is not exposed to VBA. For Example new Sort Dialog or Remove Duplicate.
3. As command button has no default value, you can do nothing with it.
4. In some case for list box index is not used. For example, font dialog box’s font style argument.  In that case use that text instead like "Bold"

A Demo

xlDialogSelectSpecial takes arguments as type_num, value_type, levels

See it in UI. You will see that this dialog box uses 2 type of controls with 2 level(Command Buttons ignored) .  A little experiment and careful observation will show you that type_num refers to 15 option button under one option group, type_num refers to 4 check box and levels refers to another option group with 2 option button.

1^st level is Option Group with 15 option button. So you use index for each option button. In fact the argument name clearly tells it is a number and UI tells that it should be within 1-15.

Next control is check box. Normally check box selects exclusive properties, but here we can overlap the options. We can select Text+Error cell in worksheets.

Usually for checkbox you use 1 or 0. But here for four check box only one argument given. How excel interprets it ? Excel marks one by one as 1,2,4,16. Mathematically any combination of these 4 number will produce unique sum. Excel uses that sum to select appropriate check box. Say you want Errors and text. You put 18 (16 for errors and 2 for text)

The third control is a option group with two option button. So the input should be 1 or 2

Application.Dialogs(xlDialogSelectSpecial).Show 3,7	Will show “Go To Special” dialog with “Formulas” button and 1st 3 check box selected. 7 is sum of first 3 check box index 1+2+4

** **

Some Examples

** **

Below are few examples. These are illustrative only and will provide some idea on using others.

** **

Paste the value of “Input” column leaving one space after Show.

For example

** **

Application.Dialogs(xlDialogActiveCellFont).Show "Arial","Bold",15,0,1,0

Dialog box constant	Argument list(s)	Input	Remarks
xlDialogActiveCellFont	font, font_style, size, strikethrough, superscript, subscript, outline, shadow, underline, color, normal, background, start_char, char_count	"Arial","Bold",15,0,1,0	This is an exception that we use actual text value for list box instead of index.
xlDialogAlignment	horiz_align, wrap, vert_align, orientation, add_indent	3,1,1,,,	Last two argument is not supported possibly. And for Merge Cells & Shrink To Fit no argument given.
xlDialogCellProtection	locked, hidden	1,0
xlDialogClear	type_num	3
xlDialogConsolidate	source_refs, function_num, top_row, left_col, create_links	Array("'2'!R1C1:R6C3","'3'!R1C1:R9C3"),1,1,0,1	The First argument takes an array of range name along with sheet name. And you have to use R1C1 style.
xlDialogCreateNames	top, left, bottom, right	1,0,1,0
xlDialogDataSeries	rowcol, type_num, date_num, step_value, stop_value, trend	2,3,4,1,5,0	The controls are inter related. For example if day option selected then 3rd option group will be active. If you set Trend to TRUE Step,Stop is inactive
xlDialogEditDelete	shift_num	4
xlDialogFilterAdvanced	operation, list_ref, criteria_ref, copy_ref, unique	2,range("a6:a10"),range("a6:a8"),range("a12"),1	The 4th Argument is valid if the first argument is 2
xlDialogFormatNumber	format_text	"000.00"	You have to give exat numberformat code like "0.00". This is an exception because normally listbox takes index as input. Posssibly because of custom number format at bottom of this dialog box it has been designed this way.
xlDialogFormulaFind	text, in_num, at_num, by_num, dir_num, match_case, match_byte	"ds",2,1,2,1,0	The 3rd Argument is one exception to normal checkbox type rule. Here 2 unchecks and 1 checks. It should be 0 and any non-zero Last argument match_byte is not in actual dialog box hence no use.
xlDialogFormulaGoto	reference, corner	range("B1")	Last argument corner is not in actual dialog box hence no use.
xlDialogFormulaReplace	find_text, replace_text, look_at, look_by, active_cell, match_case, match_byte	"as","ds",1,1,,1	The 3rd Argument is one exception to normal checkbox type rule. Here 2 unchecks and 1 checks. It should be 0 and any non-zero Last argument match_byte is not in actual dialog box hence no use. The 5th Argument not needed in user interface as user will select cell before running  code.
xlDialogGoalSeek	target_cell, target_value, variable_cell	range("a2"),8,range("c3")	The 2nd argument is only number.
xlDialogInsert	shift_num	3
xlDialogPageSetup	head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size, scale, pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft	"", "Page &P of &N", 0, 0, 0, 0.4, 0, 0, 0, 0, 2, 9, 100, 1, 1, 1, 600, 0, 0.2, 0, 0
xlDialogPasteSpecial	paste_num, operation_num, skip_blanks, transpose	1,1,1	Last option transpose not supported possibly.
xlDialogPrint	range_num, from, to, copies, draft, preview, print_what, color, feed, quality, y_resolution, selection, printer_text, print_to_file, collate	2,4,5,2,,,3,,,,,,,,1
xlDialogSaveAs	document_text, type_num, prot_pwd, backup, write_res_pwd, read_only_rec	"abcd",2,"456",1,"45",1	The second argument is xlFileFormat constants. Search offline help for getting numbers for each file format. Here 2 means SYLK
xlDialogSaveWorkbook	document_text, type_num, prot_pwd, backup, write_res_pwd, read_only_rec	"abcd",2,"456",1,"45",1	The second argument is xlFileFormat constants. Search offline help for getting numbers for each file format. Here 2 means SYLK. You can use constans like xlCSV directly.
xlDialogSelectSpecial	type_num, value_type, levels	3,21,2	This need some explanation. 1 ) It has 15 option button under one option group. You can select any of 15. First argument can be any integer from 1 to 15. 2 ) Formulas and Constants have 4 check box under it. Those are identified with 1,2,4,16 one-by-one. Number as 1, Text as 2, Logical as 4, Error as 16. If you need to default logical and Text you use 4+2=6 3) Some has one option group with 2 option button. We can select one of two. This argument can 1 or 2. This happens for Precedents, Dependents,Conditional Formats,Validations.
xlDialogSetPrintTitles	titles_for_cols_ref, titles_for_rows_ref	"RngNameo","RngNamet" or Range("A:A"),Range("1:1")	Take care that for 1st argument it should be row and for 2nd it should be entire column
xlDialogSubtotalCreate	at_change_in, function_num, total, replace, pagebreaks, summary_below	2,2,Array(2,1),1,1,0	The total argument is based on selcted columns. If 4 column then you can specify upto 4 column in Array function.
xlDialogTable	row_ref, column_ref	"RngNameo","RngNamet" or Range("A1"),Range("B2")	You can use Range("address") or Name of range
xlDialogTextToColumns	destination_ref, data_type, text_delim, consecutive_delim, tab, semicolon, comma, space, other, other_char, field_info	"RngName",1,3,0,1,1,0,0,0,""	1. The Last argument FieldInfo is Generated on actual column number after parsing. Not Possible to specify in advance. 2.The First Argument is Range Name. You can't specify address.
xlDialogUnhide	window_text	"Book2"
xlDialogWorkbookAdd	name_array, dest_book, position_num	Array("Sheet1","Sheet2"),"Book1",4	This is copying to new excel file
xlDialogWorkbookCopy	name_array, dest_book, position_num	Array("Sheet1","Sheet2"),"Book1",4	This is copying to existing opened excel
xlDialogWorkbookName	oldname_text, newname_text	"Sheet4","New"
xlDialogWorkbookProtect	structure, windows, password	TRUE,TRUE,54%21
xlDialogWorkbookUnhide	sheet_text	"Sheet5"
xlDialogZoom	magnification	"75"