Excel2000: Access user-defined graph in combo-box

[Gerrit Kiers]

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

 

[Jon Peltier]

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

 

[Gerrit Kiers]

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

 

[Jon Peltier]

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