Excel's built-in dialogs

[Dag Johansen]

Hi,

is it possible to (legally!) use Excel's dialog boxes from
VBA script? In my case I would like to display the
AutoFormat dialog box to the user. I need to read the
user's settings (chosen style + options) programatically -
the formatting will later be applied to several different
ranges.

My guess is I have to make a userform myself. So I'll be
very happy if any of you can please prove me wrong!

Regards,

Dag

 

[Jan Karel Pieterse]

Hi,

Application.Dialogs(xlDialogFormatAuto).Show

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[Dag Johansen]

Hi,

and thanks a bunch to you Jan for your response.

Now I've only got one small problem... I don't want to
perform autoformatting at the time of showing the dialog
(the range that will be formatted isn't yet known; it
depends on external data) - I just want to use it to let
the user interact with this familiar form.

So.. how can I

a) get the choices the user made in the dialog?

b) stop the dialog from performing the autoformatting?

The former is vital while the latter is no big deal; I can
always select some dummy range to autoformat and then
remove the formatting afterwards (the dialog gives an
error message if I try to show it without a more-than-one-
cell Range selected).

Best regards,

Dag

 

[Jan Karel Pieterse]

Hi,

I don't think you can do this. I found no way to read the
Autoformat choices afterwards.

I guess all you can do is FIRST make sure on what range
the autoformat must be done, THEN show the dialog. Or
build your own form <g>.

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[Tom Ogilvy]

Just to add to what Jan already stated:
Built in dialogs work the same as if you picked them from the menu manually.
The return True if the user click OK and False if the user clicks cancel.
That is about as much information as you can get from them.

Once autoformat does its formatting, their is nothing to identify that it
was done with autoformat - it doesn't create any identifiable attribute of
the range - it applies formatting cell by cell based on its internal
algorithm.

 

[Ben R. Bolton]

I need to do the same thing as Dag. I found that for Word's
Dialogs(wdDialogTableFormat) does does provide access to the data through
properties. Is it possible that Excel's Dialogs(xlDialogFormatAuto) diolog
has similar properties and they are simply un-documented? If they do exist
is there a way to determine what they are?

Ben


With Dialogs(wdDialogTableAutoFormat)
FormatChosen = .Format ' 0 - 42
BordersActivated = .Borders ' 0 or 1
ShadingActivated = .Shading ' 0 or 1
FontActivated = .Font ' 0 or 1
ColorActivated = .Color ' 0 or 1
HeadingRowsActivated = .HeadingRows ' 0 or 1
LastRowActivated = .LastRow ' 0 or 1
FirstColumnActivated = .FirstColumn ' 0 or 1
LastColumnActivated .LastColumn ' 0 or 1
AutoFitActivated = .AutoFit ' 0 or 1
End With

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba00/html/TableStyles.asp

 

[Vasant Nanavati]

Hi Ben:
has similar properties and they are simply un-documented?<<

Sorry, no.

Regards,

Vasant.