How to show excel sheet chart in excel VBA userform

user_9151274 0
Viewed 847

I have a userform with two controls. One combobox called ComboBox1 and an image called image1. I am trying to show a chart on a sheet to this image1 during ComboBox1 change event as below

Private Sub ComboBox1_Change()
  Call UpdateChart
End Sub

Private Sub UpdateChart()

Dim sTempFile As String
Dim oChart As Chart

sTempFile = Environ("temp") & "\temp.gif"

Set oChart = Worksheets(UserForm1.ComboBox1.Value).ChartObjects("Chart 1").Chart

oChart.Export Filename:=sTempFile, FilterName:="GIF"

UserForm1.Image1.Picture = LoadPicture(sTempFile)

Kill sTempFile

End Sub

This is working perfectly fine. But once I clicked on the image, ComboBox1 change event is not working anymore. In other words, the chart is not changing according to combobox change if I clicked on the image. Does anyone know why? PS: I took the above code from some other site and modified to suit my needs.

Edit 1

ComboBox1 populating as below during Userform initialization. 

Private Sub UserForm_Initialize()
With UserForm1.ComboBox1
    Dim ws As Worksheet
    For Each ws In Worksheets
        Select Case ws.Name
            Case "MainPage", "Raw Data"

            Case Else
                .AddItem ws.Name
        End Select
    Next ws
End With
End Sub

ComboBox1 values are the sheet names

vba excel
1 Answers

Try this - adding Me.Repaint fixed it for me

Private Sub UpdateChart()

    Dim sTempFile As String
    Dim oChart As Chart

    sTempFile = Environ("temp") & "\temp.gif"

    Set oChart = Worksheets(Me.ComboBox1.Value).ChartObjects("Chart 1").Chart

    oChart.Export Filename:=sTempFile, FilterName:="GIF"

    Me.Image1.Picture = LoadPicture(sTempFile)

    Me.Repaint   '<<<<<

    Kill sTempFile

End Sub

FYI within a form's code it's better to use Me instead of (eg) UserForm1 - that way you can rename the form if you need without having to edit all the code.

user_4788840
Related