Access custom chart autoformats

[John Shell]

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++Hello,
I want to access the available user-defined chart formats. I will
present the names of the formats to the user in a list for him/her to
choose one.

I found a post here that provided sample code that loaded the
xlusrgal.xls file (where the user-defined formats are stored) into a
workbook, then iterates through the charts in that workbook. This
works fine except that the contents of the file briefly flash onto the
screen until I can set it invisible. Is there any other way of
accessing the names of the format or of loading the file without it
flashing on the screen? There are methods available for adding and
deleting formats, I would think there should be methods/properties for
iterating through the formats.

Below is the code I have for loading and going through the xlusrgal
file (just adding the names to a string instead of a listbox for now).

strUsrGalFile = Application.UserLibraryPath & _
"..\Excel\XLUSRGAL.XLS"
If (Dir(strUsrGalFile) <> "") Then
Workbooks.Open strUsrGalFile, ReadOnly:=True
With Workbooks("xlusrgal.xls")
.Windows(1).Visible = False
For i = 1 To .Charts.Count
strUsrFormats = strUsrFormats & .Charts(i).Name & _
vbNewLine
Next i
MsgBox .Charts.Count & " charts" & vbNewLine & _
strUsrFormats
.Close False
End With
End If


Thanks much,
John

 

[Jon Peltier]

To avoid flashing:

Application.ScreenUpdating = False
Workbooks.Open strUsrGalFile, ReadOnly:=True
Application.ScreenUpdating = False

You can only delete or apply formats if you know their names ahead of time.
I don't think you can access the user defined chart types without opening
the user gallery workbook.

- Jon

 

[John Shell]

Thanks a lot, Jon. I thought I looked through all of the properties
and methods of Application, but somehow managed to miss that one.
Excel VBA isn't exactly my forte (I'm mainly a C++ person), so the
help is greatly appreciated.

John Shell