That workbook from jolivanes is superb...really nice.
Here is what I did recently regarding this topic:
(1)
Established a dedicated folder and path named "C:\Your\ChartFile\Path\" (though the code can be modified to delete and re-establish any directory name).
In the userform's Initialization event...
(2)
...Delete all chart photo files (actually all files to be on the safe side) from your dedicated folder.
(3)
...Repopulate your dedicated folder with updated chart photos. This gives the userform presentation more meaning because the most recent chart data is shown.
(4)
...Embedded chart objects are strange because their name involves the parent sheet name. I parsed that sheet name out of the chart name for a better-looking chart name that will show up in the ComboBox, without the .gif extension.
(5)
...Populate the ComboBox with the chart photo file names, without the extension.
(6)
The ComboBox change event populates an info label and loads the selected file name onto the Image control. I used a ComboBox instead of command buttons because oftentimes people want to see the names of what they are selecting, especially if the audience requests a specific file to be seen, instead of flipping through the in-between ones as with a command button. That said, I repeat that the workbook from jolivanes is really well done, just excellent.
I know some people cannot, or are not allowed to, download attached files, so here is a description of my userform and the code:
There are 5 controls:
1) Label header at the top, advising user to select a file name from the ComboBox.
2) ComboBox to select a file name
3) Label (optional) below the CoimboBox and above the Image control, displaying the name of the file photo for the viewer's benefit (triggered by cbo Change).
4) Image control
5) Exit button.
The workbook itself has 5 chart examples (all live), one on each worksheet, though you can have as many as you want. I did not involve chart sheets with this; I never use those.
Here is the code, with notes. If anyone is interested in seeing the actual workbook, PM me and I will be happy to send you a copy. It is one of several I created for my upcoming web site that would be a free download.
Option Explicit
'Tip - - Beforehand, give meaningful names to your charts with code like this:
'Sheet1.ChartObjects(1).Name = "Revenue Sources"
'Sheet2.ChartObjects(1).Name = "Month Quantity"
'Sheet3.ChartObjects(1).Name = "Sales Dollars"
'Sheet4.ChartObjects(1).Name = "Expenses Distribution"
'Sheet5.ChartObjects(1).Name = "Inventory by year"
Private Sub UserForm_Initialize()
'Step 1
'Open a With structure for the Application object and prepare Excel.
With Application
.ScreenUpdating = False
.EnableEvents = False
'Step 2
'Declare and define variables.
Dim MyPath As String, ASN As String, ASC As String, ACN As String, ACNN As String
Dim ws As Worksheet, ChObj As Object
Dim i As Integer, n As Integer, ASCLen As Integer
Dim fA() As String, dName As String
MyPath = "C:\Your\ChartFile\Path\"
ASN = ActiveSheet.Name
'Step 3
'Delete all chart photo files (actually all files to be on the safe side) from your folder.
With .FileSearch
.NewSearch
.LookIn = MyPath
.FileType = 1
.SearchSubFolders = True
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Kill .FoundFiles(i)
Next i
End If
End With
'Step 4
'Repopulate your folder with updated chart photos.
For Each ws In Worksheets
'a
ws.Activate 'Sheet activated so chart can be activated.
For Each ChObj In ws.ChartObjects
'b
ASC = ActiveSheet.Name 'Sheet name length will need to be known in Step 4c.
'c
ASCLen = Len(ASC) 'Sheet name length defined, invoked in Step 4f.
'd
ChObj.Activate 'Chart must be activated to be exported.
'e
ACN = ActiveChart.Name 'Entire chart name includes parent sheet name and chart name.
'f
ACNN = Right(ACN, Len(ACN) - ASCLen - 1) 'Parse out the sheet name for a true chart object name.
'g
ActiveChart.Export MyPath & ACNN & ".gif" 'Save the chart object name as a .gif to folder defined in Step 2.
'h
Range("A1").Activate 'Deactivate the chart object by activating a worksheet cell.
Next ChObj 'For some versions, this is a necessary step.
Next ws
'Step 5
'End up on the sheet you were on when you called the userform, for the user's convenience.
Sheets(ASN).Activate
'Step 6
'Load the ComboBox with the names of the chart photos
ChDir MyPath
dName = Dir("*") 'All files (which will only be the 'gif's from Step 4g).
Do While dName <> ""
n = n + 1
ReDim Preserve fA(1 To n)
fA
= dName
dName = Dir()
Loop
For i = 1 To n
cboChartNames.AddItem Left(fA(i), .Find(".", fA(i)) - 1) 'True chart object name without .gif extension
Next 'for cleaner look in ComboBox.
'Step 7
'Restore Excel and close the With structure for the Application object.
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Private Sub cboChartNames_Change()
'Notice the " & ".gif" " code, to properly refer to the ComboBox item
'that was loaded into the ComboBox in Step 6 of the Inialization event.
imgShowCharts.Picture = LoadPicture(cboChartNames.Value & ".gif")
'A label above the Image control is optional, to serve as a header
'for the Image, identifying each new chart as it is selected and displayed.
Me.lblChartName.Caption = "This chart's name is ''" & cboChartNames.Value & "''."
End Sub
Private Sub cmdExit_Click()
Unload Me
End Sub
