Hi all

I have a macro that applies a user-defined chart template to a basic chart.
Ideally I would like to be able to do one of the following:

1. Take a constant/variable that contains the name of the template and
compare this against a list of available user-defined templates to see if it
exists.

and/or

2. Create a user form (which I think I can do) that is basically a stripped
down version of the xlDialogChartType dialog box and just contains a list of
available user-defined templates for the user to select. I would prefer not
to use the 'official' dialog box as it involves clicking through several tabs
to get to the user-defined list (unless you can get it to display that
straight away of course)., which may not seem like too much of a chore but
the users will be running this a lot so it will get frustrating.

Thank you for any help

Andy

Hi Andy,

Many Dialogs accept argument(s) to instruct what tab or option is selected
when opened. But xlDialogChartType does not appear to have any arg's.

So I think the only way would be to open xlusrgal.xls and make a list of all
the chart sheets. Location is version specific. First would need to check
the file exists, it won't if there are no custom UD charts. Best to open it
as ReadOnly and keep it not visible, close it when done.

Regards,
Peter T

"Andy" wrote in message
...
Hi all

I have a macro that applies a user-defined chart template to a basic
chart.
Ideally I would like to be able to do one of the following:

1. Take a constant/variable that contains the name of the template and
compare this against a list of available user-defined templates to see if
it
exists.

and/or

2. Create a user form (which I think I can do) that is basically a
stripped
down version of the xlDialogChartType dialog box and just contains a list
of
available user-defined templates for the user to select. I would prefer
not
to use the 'official' dialog box as it involves clicking through several
tabs
to get to the user-defined list (unless you can get it to display that
straight away of course)., which may not seem like too much of a chore but
the users will be running this a lot so it will get frustrating.

Thank you for any help

Andy

Hi,

Does this builtin dialog help at all?
Application.Dialogs(xlDialogGalleryCustom).Show

Cheers
Andy

Andy wrote:
Hi all

I have a macro that applies a user-defined chart template to a basic chart.
Ideally I would like to be able to do one of the following:

1. Take a constant/variable that contains the name of the template and
compare this against a list of available user-defined templates to see if it
exists.

and/or

2. Create a user form (which I think I can do) that is basically a stripped
down version of the xlDialogChartType dialog box and just contains a list of
available user-defined templates for the user to select. I would prefer not
to use the 'official' dialog box as it involves clicking through several tabs
to get to the user-defined list (unless you can get it to display that
straight away of course)., which may not seem like too much of a chore but
the users will be running this a lot so it will get frustrating.

Thank you for any help

Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy

Unfortunately not, I'm not sure why but I get a run-time error 1004 and the
message 'Show method of Dialog failed'. I guess this is the dialog box that
is displayed but might it have to be opened as part of the main chart type
selection dialog rather than just on its own?

Thanks

Andy

"Andy Pope" wrote:

Hi,

Does this builtin dialog help at all?
Application.Dialogs(xlDialogGalleryCustom).Show

Cheers
Andy

Andy wrote:
Hi all

I have a macro that applies a user-defined chart template to a basic chart.
Ideally I would like to be able to do one of the following:

1. Take a constant/variable that contains the name of the template and
compare this against a list of available user-defined templates to see if it
exists.

and/or

2. Create a user form (which I think I can do) that is basically a stripped
down version of the xlDialogChartType dialog box and just contains a list of
available user-defined templates for the user to select. I would prefer not
to use the 'official' dialog box as it involves clicking through several tabs
to get to the user-defined list (unless you can get it to display that
straight away of course)., which may not seem like too much of a chore but
the users will be running this a lot so it will get frustrating.

Thank you for any help

Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy,

I was just about to post I thought Any Pope's suggestion would be perfect
for your needs, I didn't spot that dialog!

I suspect the reason for your error is along the lines of what I mentioned
in my post.

Sub test()
On Error Resume Next
Application.Dialogs(xlDialogGalleryCustom).Show
If Err.Number Then
MsgBox "probably no UD custom charts exist and" & vbCr & _
"therefore neither does xlusrgal.xls"
End If
On Error GoTo 0
End Sub

Regards,
Peter T

"Andy" wrote in message
...
Hi Andy

Unfortunately not, I'm not sure why but I get a run-time error 1004 and
the
message 'Show method of Dialog failed'. I guess this is the dialog box
that
is displayed but might it have to be opened as part of the main chart type
selection dialog rather than just on its own?

Thanks

Andy

"Andy Pope" wrote:

Hi,

Does this builtin dialog help at all?
Application.Dialogs(xlDialogGalleryCustom).Show

Cheers
Andy

Andy wrote:
Hi all

I have a macro that applies a user-defined chart template to a basic
chart.
Ideally I would like to be able to do one of the following:

1. Take a constant/variable that contains the name of the template and
compare this against a list of available user-defined templates to see
if it
exists.

and/or

2. Create a user form (which I think I can do) that is basically a
stripped
down version of the xlDialogChartType dialog box and just contains a
list of
available user-defined templates for the user to select. I would
prefer not
to use the 'official' dialog box as it involves clicking through
several tabs
to get to the user-defined list (unless you can get it to display that
straight away of course)., which may not seem like too much of a chore
but
the users will be running this a lot so it will get frustrating.

Thank you for any help

Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Peter

No, I missed that one too (obviously!) - I was only looking at the ones with
chart in the title.

I'm actually in the process of trying your method out and with some success.
The file definitely exists and contains four templates so that is not what
was causing the error.

So far I've created a function that extracts the template names from the
workbook and returns them in a string (I might change it to return an
array?). I can then either use this as the data source for a list box (if
Excel works in the same way as Access) or use it to check that a given
template exists on the user's PC.

Thanks
Andy

"Peter T" wrote:

Hi Andy,

I was just about to post I thought Any Pope's suggestion would be perfect
for your needs, I didn't spot that dialog!

I suspect the reason for your error is along the lines of what I mentioned
in my post.

Sub test()
On Error Resume Next
Application.Dialogs(xlDialogGalleryCustom).Show
If Err.Number Then
MsgBox "probably no UD custom charts exist and" & vbCr & _
"therefore neither does xlusrgal.xls"
End If
On Error GoTo 0
End Sub

Regards,
Peter T

"Andy" wrote in message
...
Hi Andy

Unfortunately not, I'm not sure why but I get a run-time error 1004 and
the
message 'Show method of Dialog failed'. I guess this is the dialog box
that
is displayed but might it have to be opened as part of the main chart type
selection dialog rather than just on its own?

Thanks

Andy

"Andy Pope" wrote:

Hi,

Does this builtin dialog help at all?
Application.Dialogs(xlDialogGalleryCustom).Show

Cheers
Andy

Andy wrote:
Hi all

I have a macro that applies a user-defined chart template to a basic
chart.
Ideally I would like to be able to do one of the following:

1. Take a constant/variable that contains the name of the template and
compare this against a list of available user-defined templates to see
if it
exists.

and/or

2. Create a user form (which I think I can do) that is basically a
stripped
down version of the xlDialogChartType dialog box and just contains a
list of
available user-defined templates for the user to select. I would
prefer not
to use the 'official' dialog box as it involves clicking through
several tabs
to get to the user-defined list (unless you can get it to display that
straight away of course)., which may not seem like too much of a chore
but
the users will be running this a lot so it will get frustrating.

Thank you for any help

Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi,

Sorry I should have pointed out you need to have the chart active for
the dialog to show without the error.

Cheers
Andy

Andy wrote:
Hi Andy

Unfortunately not, I'm not sure why but I get a run-time error 1004 and the
message 'Show method of Dialog failed'. I guess this is the dialog box that
is displayed but might it have to be opened as part of the main chart type
selection dialog rather than just on its own?

Thanks

Andy

"Andy Pope" wrote:


Hi,

Does this builtin dialog help at all?
Application.Dialogs(xlDialogGalleryCustom).Sho w

Cheers
Andy

Andy wrote:

Hi all

I have a macro that applies a user-defined chart template to a basic chart.
Ideally I would like to be able to do one of the following:

1. Take a constant/variable that contains the name of the template and
compare this against a list of available user-defined templates to see if it
exists.

and/or

2. Create a user form (which I think I can do) that is basically a stripped
down version of the xlDialogChartType dialog box and just contains a list of
available user-defined templates for the user to select. I would prefer not
to use the 'official' dialog box as it involves clicking through several tabs
to get to the user-defined list (unless you can get it to display that
straight away of course)., which may not seem like too much of a chore but
the users will be running this a lot so it will get frustrating.

Thank you for any help

Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy,

The only other thing I can think of that would cause it to fail is if a
chart is not active at the time.

Yes, an array of names would be good as source data for your Listbox.

or use it to check that a given
template exists on the user's PC.

Not quite sure what you mean here. You would need to have obtained the list
of UD charts from the user's user gallery (xlusrgal.xls) so you have already
confirmed they exist. If you are comparing names with your own pre-defined
list you can't be sure they refer to similar charts.

Regards,
Peter T


"Andy" wrote in message
...
Hi Peter

No, I missed that one too (obviously!) - I was only looking at the ones
with
chart in the title.

I'm actually in the process of trying your method out and with some
success.
The file definitely exists and contains four templates so that is not
what
was causing the error.

So far I've created a function that extracts the template names from the
workbook and returns them in a string (I might change it to return an
array?). I can then either use this as the data source for a list box (if
Excel works in the same way as Access) or use it to check that a given
template exists on the user's PC.

Thanks
Andy

"Peter T" wrote:

Hi Andy,

I was just about to post I thought Any Pope's suggestion would be
perfect
for your needs, I didn't spot that dialog!

I suspect the reason for your error is along the lines of what I
mentioned
in my post.

Sub test()
On Error Resume Next
Application.Dialogs(xlDialogGalleryCustom).Show
If Err.Number Then
MsgBox "probably no UD custom charts exist and" & vbCr & _
"therefore neither does xlusrgal.xls"
End If
On Error GoTo 0
End Sub

Regards,
Peter T

"Andy" wrote in message
...
Hi Andy

Unfortunately not, I'm not sure why but I get a run-time error 1004
and
the
message 'Show method of Dialog failed'. I guess this is the dialog
box
that
is displayed but might it have to be opened as part of the main chart
type
selection dialog rather than just on its own?

Thanks

Andy

"Andy Pope" wrote:

Hi,

Does this builtin dialog help at all?
Application.Dialogs(xlDialogGalleryCustom).Show

Cheers
Andy

Andy wrote:
Hi all

I have a macro that applies a user-defined chart template to a
basic
chart.
Ideally I would like to be able to do one of the following:

1. Take a constant/variable that contains the name of the template
and
compare this against a list of available user-defined templates to
see
if it
exists.

and/or

2. Create a user form (which I think I can do) that is basically a
stripped
down version of the xlDialogChartType dialog box and just contains
a
list of
available user-defined templates for the user to select. I would
prefer not
to use the 'official' dialog box as it involves clicking through
several tabs
to get to the user-defined list (unless you can get it to display
that
straight away of course)., which may not seem like too much of a
chore
but
the users will be running this a lot so it will get frustrating.

Thank you for any help

Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

You are correct - see Andy's note.

I think I will use the builtin dialog so the rest is pretty much academic
now. I was going to compare the list on the user's machine with a
pre-defined constant. The template would either exist on their machine with
the correct name or not at all because I will have added the template in the
first place.

Thanks for all your help - even though I'm (probably) not going to use the
XLUSRGAL.xls file I now know it is there and what it is for!

Andy

"Peter T" wrote:

Hi Andy,

The only other thing I can think of that would cause it to fail is if a
chart is not active at the time.

Yes, an array of names would be good as source data for your Listbox.

or use it to check that a given
template exists on the user's PC.

Not quite sure what you mean here. You would need to have obtained the list
of UD charts from the user's user gallery (xlusrgal.xls) so you have already
confirmed they exist. If you are comparing names with your own pre-defined
list you can't be sure they refer to similar charts.

Regards,
Peter T


"Andy" wrote in message
...
Hi Peter

No, I missed that one too (obviously!) - I was only looking at the ones
with
chart in the title.

I'm actually in the process of trying your method out and with some
success.
The file definitely exists and contains four templates so that is not
what
was causing the error.

So far I've created a function that extracts the template names from the
workbook and returns them in a string (I might change it to return an
array?). I can then either use this as the data source for a list box (if
Excel works in the same way as Access) or use it to check that a given
template exists on the user's PC.

Thanks
Andy

"Peter T" wrote:

Hi Andy,

I was just about to post I thought Any Pope's suggestion would be
perfect
for your needs, I didn't spot that dialog!

I suspect the reason for your error is along the lines of what I
mentioned
in my post.

Sub test()
On Error Resume Next
Application.Dialogs(xlDialogGalleryCustom).Show
If Err.Number Then
MsgBox "probably no UD custom charts exist and" & vbCr & _
"therefore neither does xlusrgal.xls"
End If
On Error GoTo 0
End Sub

Regards,
Peter T

"Andy" wrote in message
...
Hi Andy

Unfortunately not, I'm not sure why but I get a run-time error 1004
and
the
message 'Show method of Dialog failed'. I guess this is the dialog
box
that
is displayed but might it have to be opened as part of the main chart
type
selection dialog rather than just on its own?

Thanks

Andy

"Andy Pope" wrote:

Hi,

Does this builtin dialog help at all?
Application.Dialogs(xlDialogGalleryCustom).Show

Cheers
Andy

Andy wrote:
Hi all

I have a macro that applies a user-defined chart template to a
basic
chart.
Ideally I would like to be able to do one of the following:

1. Take a constant/variable that contains the name of the template
and
compare this against a list of available user-defined templates to
see
if it
exists.

and/or

2. Create a user form (which I think I can do) that is basically a
stripped
down version of the xlDialogChartType dialog box and just contains
a
list of
available user-defined templates for the user to select. I would
prefer not
to use the 'official' dialog box as it involves clicking through
several tabs
to get to the user-defined list (unless you can get it to display
that
straight away of course)., which may not seem like too much of a
chore
but
the users will be running this a lot so it will get frustrating.

Thank you for any help

Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy

That seems to be working like a dream! Thank you very much.

One final thing, is there a way of capturing the name of the chart template
selected by the user from the dialog box? The reason is because in anyone
session the user is likely to be using the same template. If I can identify
which one they have selected then I can store it in a global or static
variable so the user only needs to select it once per session.

Thanks again
Andy


"Andy Pope" wrote:

Hi,

Sorry I should have pointed out you need to have the chart active for
the dialog to show without the error.

Cheers
Andy

Andy wrote:
Hi Andy

Unfortunately not, I'm not sure why but I get a run-time error 1004 and the
message 'Show method of Dialog failed'. I guess this is the dialog box that
is displayed but might it have to be opened as part of the main chart type
selection dialog rather than just on its own?

Thanks

Andy

"Andy Pope" wrote:


Hi,

Does this builtin dialog help at all?
Application.Dialogs(xlDialogGalleryCustom).Sho w

Cheers
Andy

Andy wrote:

Hi all

I have a macro that applies a user-defined chart template to a basic chart.
Ideally I would like to be able to do one of the following:

1. Take a constant/variable that contains the name of the template and
compare this against a list of available user-defined templates to see if it
exists.

and/or

2. Create a user form (which I think I can do) that is basically a stripped
down version of the xlDialogChartType dialog box and just contains a list of
available user-defined templates for the user to select. I would prefer not
to use the 'official' dialog box as it involves clicking through several tabs
to get to the user-defined list (unless you can get it to display that
straight away of course)., which may not seem like too much of a chore but
the users will be running this a lot so it will get frustrating.

Thank you for any help

Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

I can not see anything that would reveal the Name used :(


Andy wrote:
Hi Andy

That seems to be working like a dream! Thank you very much.

One final thing, is there a way of capturing the name of the chart template
selected by the user from the dialog box? The reason is because in anyone
session the user is likely to be using the same template. If I can identify
which one they have selected then I can store it in a global or static
variable so the user only needs to select it once per session.

Thanks again
Andy


"Andy Pope" wrote:


Hi,

Sorry I should have pointed out you need to have the chart active for
the dialog to show without the error.

Cheers
Andy

Andy wrote:

Hi Andy

Unfortunately not, I'm not sure why but I get a run-time error 1004 and the
message 'Show method of Dialog failed'. I guess this is the dialog box that
is displayed but might it have to be opened as part of the main chart type
selection dialog rather than just on its own?

Thanks

Andy

"Andy Pope" wrote:



Hi,

Does this builtin dialog help at all?
Application.Dialogs(xlDialogGalleryCustom).Sho w

Cheers
Andy

Andy wrote:


Hi all

I have a macro that applies a user-defined chart template to a basic chart.
Ideally I would like to be able to do one of the following:

1. Take a constant/variable that contains the name of the template and
compare this against a list of available user-defined templates to see if it
exists.

and/or

2. Create a user form (which I think I can do) that is basically a stripped
down version of the xlDialogChartType dialog box and just contains a list of
available user-defined templates for the user to select. I would prefer not
to use the 'official' dialog box as it involves clicking through several tabs
to get to the user-defined list (unless you can get it to display that
straight away of course)., which may not seem like too much of a chore but
the users will be running this a lot so it will get frustrating.

Thank you for any help

Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy

Nevermind, they'll just have to do some work for a change!!! Thanks for all
your help.

Andy

"Andy Pope" wrote:

I can not see anything that would reveal the Name used :(


Andy wrote:
Hi Andy

That seems to be working like a dream! Thank you very much.

One final thing, is there a way of capturing the name of the chart template
selected by the user from the dialog box? The reason is because in anyone
session the user is likely to be using the same template. If I can identify
which one they have selected then I can store it in a global or static
variable so the user only needs to select it once per session.

Thanks again
Andy


"Andy Pope" wrote:


Hi,

Sorry I should have pointed out you need to have the chart active for
the dialog to show without the error.

Cheers
Andy

Andy wrote:

Hi Andy

Unfortunately not, I'm not sure why but I get a run-time error 1004 and the
message 'Show method of Dialog failed'. I guess this is the dialog box that
is displayed but might it have to be opened as part of the main chart type
selection dialog rather than just on its own?

Thanks

Andy

"Andy Pope" wrote:



Hi,

Does this builtin dialog help at all?
Application.Dialogs(xlDialogGalleryCustom).Sho w

Cheers
Andy

Andy wrote:


Hi all

I have a macro that applies a user-defined chart template to a basic chart.
Ideally I would like to be able to do one of the following:

1. Take a constant/variable that contains the name of the template and
compare this against a list of available user-defined templates to see if it
exists.

and/or

2. Create a user form (which I think I can do) that is basically a stripped
down version of the xlDialogChartType dialog box and just contains a list of
available user-defined templates for the user to select. I would prefer not
to use the 'official' dialog box as it involves clicking through several tabs
to get to the user-defined list (unless you can get it to display that
straight away of course)., which may not seem like too much of a chore but
the users will be running this a lot so it will get frustrating.

Thank you for any help

Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info