Displaying A Chart In A Userform

– Selva V Pasupathy, HSBC Global Resourcing

Original Source: John Walkenbach

I was pretty impressed when I came across a page where John has mentioned how you can use userform to show all the chrts in a spreadsheet on a userform and you can move betwee different charts with the help of a commandbutton.

Image showing how your userform with chart will look like

HOW SHOULD I USE THE FOLLOWING CODE

Copy the code below and paste in code section of a userform

Dim ChartNum As Integer

Private Sub UserForm_Initialize()
    ChartNum = 1
    UpdateChart
End Sub

Private Sub PreviousButton_Click()
    If ChartNum = 1 Then ChartNum = 3 Else ChartNum = ChartNum - 1
    UpdateChart
End Sub

Private Sub NextButton_Click()
    If ChartNum = 3 Then ChartNum = 1 Else ChartNum = ChartNum + 1
    UpdateChart
End Sub

Private Sub CloseButton_Click()
    Unload Me
End Sub

Private Sub UpdateChart()
    Set CurrentChart = Sheets("Charts").ChartObjects(ChartNum).Chart
    CurrentChart.Parent.Width = 300
    CurrentChart.Parent.Height = 150

'   Save chart as GIF
    Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
    CurrentChart.Export FileName:=Fname, FilterName:="GIF"

'   Show the chart
    Image1.Picture = LoadPicture(Fname)
End Sub

Copy the code below and paste in a code module.

Sub ShowChart()
    UserForm1.Show
End Sub

Download the example file from here CLICK HERE

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: