CODEWITHSUNDEEP
vbaexcelchartsuserform
Heaviside
Heaviside

Reputation: 13

VBA Userform: Accessing different Charts depending on Combobox

I am a beginner with VBA, and only have some experience with MATlab.

At the moment I am trying to use a Userform to display different graphs (on that same userform), depending on the output of a ComboBox. I am basing my chart display on this tutorial. In essence the picture is saved as a GIF and then opened with an Image Control.

I have my charts saved as Chart#_####, for example; Chart1_4301. The sequence of numbers is the same as the options in the ComboBox - I want 1_4301 in the Combobox to set the CurrentChart to Chart1_4301 and then to run through the save GIF and load image control steps

Private Sub Open_Graph_But_Click()
'This sub opens a different graph depending on the combobox selection

Set CurrentChart = "Chart" & ComboBox1.Value


CurrentChart.Parent.Width = 900
CurrentChart.Parent.Height = 450

'   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 Subb

I don't know if its possible to loop through different names of graphs, and I have tried looking up how its done, but I don't know what this is suppose to be called so its hard for me to find something useful.

What I have found has led me to the above setup, but I get a Run-time error '13': Type Mismatch, where Set CurrentChart = "Chart" & ComboBox1.Value is highlighted. Any advice is appreciated!

Upvotes: 0

Views: 2937

Answers (1)

Vikas
Vikas

Reputation: 805

I order to explain it, I have created the following macro. I have four charts in the active sheet, Chart 1, Chart 2, Chart 3, and Chart 4. In cell B23, I am entering the "Chart Name" as Chart 1/2/3 or 4, on the basis of which, my following macro works. Whatever chart I enter, it sets that selected chart as Red background, and rest 3 are formatted as yellow background. I couldn't attach my book coz I couldn't find a way to do it. If you still don't understand how to do it, send me your email and i will upload the workbook.

Sub RunMacro()

Dim sht As Worksheet
Dim co As ChartObject

Dim selectedChart As String

Set sht = ActiveSheet ' it may be your sheet like Worksheets("Sheet1") or Worksheets("Sheet2")

selectedChart = sht.Range("B23").Value ' assign the selected chart from combobox

Dim chrt As Chart

'''option 1: Loop through all the charts


For Each co In sht.ChartObjects
    If co.Name = selectedChart Then
        co.Chart.ChartArea.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
    Else
        co.Chart.ChartArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
    End If
Next co

'option 2: select the chart directly and popup the size




Set chrt = sht.ChartObjects(selectedChart).Chart

MsgBox "Selected Chart is " & selectedChart & " and the chart is for " & chrt.ChartTitle.Text

End Sub

Hope this helps. Vikas B

Upvotes: 1

Related Questions