Excel2000: Access user-defined graph in combo-box

Posted 24 hours ago on ms.public.office.vba, but got no reply:

Hi

I'm creating a type of auto-graph routine in VBA Excel 2000 and I want
it to remain very flexible. I decided that I would code only very
limited graph formatting.

I came up with a concept that uses user-defined graphs (build-in
capacity of Excel). During the execution of the macro one can select
from a combobox from the list of user-defined graphs and the selected
will be applied. Of course the user is encouraged to add own
user-defined graphs.

Since I am dealing with time-line graphs this concept has the big
advantage that I can refrain from using any date-formatting!

I still have several questions:

I might seem stupid, but I have been looking for a way to load the
combobox without opening the file.
For Each GraphObject in Workbooks("xlusrgal.xls")
would not work, since the file is no member of Workbooks.
Is there a method that I can use or do I really have to open the file
to read out the ChartObjects Collection?

How do I define the path of the "xlusrgal.xls" file, using system
variables, so that it is easy to find, no matter wether Win9x or NT
etc. (root/.../application data/..../MS/Excel/)

And I still have a doubt whether it is a good idea to use the native
"xlusrgal.xls" for this storage opposite to creating my own template
collection. But then: How do I store and retrieve? With Copy and
Paste? What would be wise?

Thanks! Gerrit

Gerrit -

It seems that Excel opens the built-in gallery workbook (xl8galry.xls in
Excel 97) in the VB Editor when you bring up the apply custom types
dialog, and when you select user-defined from the options, it also opens
the user-defined gallery (xlusrgal.xls). If you assign even a
non-existent chart type, the gallery opens. This macro opens the
gallery and displays a list of chart types (it's only been tried in
Excel 97 here at work).

Sub OpenUserGallery()
Dim cht As Chart
Dim msg As String
On Error Resume Next
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, _
TypeName:="hello"
On Error GoTo 0
With Workbooks("xlusrgal.xls")
For Each cht In .Charts
msg = cht.Name & vbCrLf
Next
End With
MsgBox msg
End Sub

If you make your own template (or other file as a holder of charts), you
could store charts in it, then copy them and use Paste Special - Formats
to apply the formatting to new charts. I don't think you can avoid
opening the file that contains your charts, but you can keep it hidden.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Gerrit Kiers wrote:

> Posted 24 hours ago on ms.public.office.vba, but got no reply:
>
> Hi
>
> I'm creating a type of auto-graph routine in VBA Excel 2000 and I want
> it to remain very flexible. I decided that I would code only very
> limited graph formatting.
>
> I came up with a concept that uses user-defined graphs (build-in
> capacity of Excel). During the execution of the macro one can select
> from a combobox from the list of user-defined graphs and the selected
> will be applied. Of course the user is encouraged to add own
> user-defined graphs.
>
> Since I am dealing with time-line graphs this concept has the big
> advantage that I can refrain from using any date-formatting!
>
> I still have several questions:
>
> I might seem stupid, but I have been looking for a way to load the
> combobox without opening the file.
> For Each GraphObject in Workbooks("xlusrgal.xls")
> would not work, since the file is no member of Workbooks.
> Is there a method that I can use or do I really have to open the file
> to read out the ChartObjects Collection?
>
> How do I define the path of the "xlusrgal.xls" file, using system
> variables, so that it is easy to find, no matter wether Win9x or NT
> etc. (root/.../application data/..../MS/Excel/)
>
> And I still have a doubt whether it is a good idea to use the native
> "xlusrgal.xls" for this storage opposite to creating my own template
> collection. But then: How do I store and retrieve? With Copy and
> Paste? What would be wise?
>
> Thanks! Gerrit
>

Hi Jon,

I noticed these opened in the VBA Editor, but I failed to notice they
did became member of the workbooks collection. Nice tip!

And it works here in Excel 2000 as well.

(msg = msg + cht.Name & vbCrLf)

Gerrit

On Thu, 20 Nov 2003 14:30:46 -0500, Jon Peltier
<jonpeltierNOSPAM@yahoo.com> wrote:

>Sub OpenUserGallery()
> Dim cht As Chart
> Dim msg As String
> On Error Resume Next
> ActiveChart.ApplyCustomType ChartType:=xlUserDefined, _
> TypeName:="hello"
> On Error GoTo 0
> With Workbooks("xlusrgal.xls")
> For Each cht In .Charts
> msg = cht.Name & vbCrLf
> Next
> End With
> MsgBox msg
>End Sub

Gerrit -

Oh yeah, I forgot to append the new chart name to the existing message.
Use & instead of + for concatenating strings, to assure it will always
concatenate.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Gerrit Kiers wrote:
> Hi Jon,
>
> I noticed these opened in the VBA Editor, but I failed to notice they
> did became member of the workbooks collection. Nice tip!
>
> And it works here in Excel 2000 as well.
>
> (msg = msg + cht.Name & vbCrLf)
>
> Gerrit
>
> On Thu, 20 Nov 2003 14:30:46 -0500, Jon Peltier
> <jonpeltierNOSPAM@yahoo.com> wrote:
>
>
>>Sub OpenUserGallery()
>> Dim cht As Chart
>> Dim msg As String
>> On Error Resume Next
>> ActiveChart.ApplyCustomType ChartType:=xlUserDefined, _
>> TypeName:="hello"
>> On Error GoTo 0
>> With Workbooks("xlusrgal.xls")
>> For Each cht In .Charts
>> msg = cht.Name & vbCrLf
>> Next
>> End With
>> MsgBox msg
>>End Sub
>
>