Excel's built-in dialogs
 

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

Excel's built-in dialogs
 

Hi,

Application.Dialogs(xlDialogFormatAuto).Show

Regards,

Jan Karel Pieterse
Excel TA/MVP

-----Original Message-----
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
.

Excel's built-in dialogs
 

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

-----Original Message-----
Hi,

Application.Dialogs(xlDialogFormatAuto).Show

Regards,

Jan Karel Pieterse
Excel TA/MVP

-----Original Message-----
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
.

.

Excel's built-in dialogs
 

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

-----Original Message-----
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

-----Original Message-----
Hi,

Application.Dialogs(xlDialogFormatAuto).Show

Regards,

Jan Karel Pieterse
Excel TA/MVP

-----Original Message-----
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
.

.

.

Excel's built-in dialogs
 

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.

--
Regards,
Tom Ogilvy


"Dag Johansen" wrote in message
...
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

-----Original Message-----
Hi,

Application.Dialogs(xlDialogFormatAuto).Show

Regards,

Jan Karel Pieterse
Excel TA/MVP

-----Original Message-----
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
.

.

Excel's built-in dialogs
 

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/de...ableStyles.asp

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Dag Johansen" wrote in message
...
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

-----Original Message-----
Hi,

Application.Dialogs(xlDialogFormatAuto).Show

Regards,

Jan Karel Pieterse
Excel TA/MVP

-----Original Message-----
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
.

.

Excel's built-in dialogs
 

Hi Ben:

Is it possible that Excel's Dialogs(xlDialogFormatAuto) diolog
has similar properties and they are simply un-documented?<<

Sorry, no.

Regards,

Vasant.

"Ben R. Bolton" wrote in message
...
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/de...ableStyles.asp

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Dag Johansen" wrote in message
...
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

-----Original Message-----
Hi,

Application.Dialogs(xlDialogFormatAuto).Show

Regards,

Jan Karel Pieterse
Excel TA/MVP

-----Original Message-----
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
.

.