The Dialogs collection

[James Butler]

Hi,

I think this may not be possible but I thought I would ask just in
case.

Can I get the results from a built in dialog box?

for example if I show the xlDialogActiveCellFont dialog box and then
the user selects which font, size etc they want, can I pass the
results to variables to be used at a later date? I have worked out
that I can evaluate the results after the user has changed the font by
using the following code:

Sub test()
Range("A1").Select
Application.Dialogs(xlDialogActiveCellFont).Show
font1 = Range("A1").Font.Name
End Sub

The problem is that I don't want the spreadsheet changed at all until
a later date. So before I go off and create my own user forms i
thought I would check to see if this is possible?

Cheers in advance.

James

 

[Rob Bovey]

Hi James,

The call: Application.Dialogs(xlDialogActiveCellFont).Show will return
True or False depending whether or not the user clicked the OK or Cancel
button. This is the extent of information you can derive from this method.
If the user clicks OK, whatever selection they made in the dialog will take
effect immediately.

If you want to delay the effect of the user's selection, thing get a bit
complicated. Assuming you know the cell(s) the user has selected prior to
invoking the method call above (which seems highly probable), you can store
all initial format conditions of those cells prior to showing the dialog.

If the method call returns True, meaning the user clicked OK, you will
need to store the resulting difference in the formats that the user selected
in the dialog by reading the state of those cells and then restore the
previous format. You can then commit the user's selected format whenever
you're ready, since you have this information stored.

For a single cell that doesn't contain anything unusual, multiple font
styles within the same cell for example, this is not too complicated. But
the level of complexity increases with the number of cells formatted as well
as the complexity of the formats. Hope that helps. Post back if there's
anything that isn't clear.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[NickHK]

James,
Instead of using Excel's built in dialogs, you could use the Windows version via the API:

Declare Function ChooseFont Lib "comdlg32.dll" Alias "ChooseFontA" (pChoosefont As CHOOSEFONT) As Long

You then get all the font info without applying it to a cell.

NickHK