Dynamic Chart On VBA

JuniorExcel · Jan 17, 2014

Hi everybody!
I request your help. I used 3 named ranges to create a dynamic chart on excel 2007.
The set of data are exported from a sheet to another through a combo box (user form).
It works from time to time because I often get an error message (after “upgrading” my user form for example) and have to redefine the chart's data source.
Do you know how to avoid that? Should I define those ranges on VBA, in that case how to do it?
Thank you for your attention.

Jon Peltier · Jan 24, 2014

Use your judgement. If the first topic was finished, then close it. I don't know if you need any kind of category.

JuniorExcel · Jan 26, 2014

Hi,
actually the 1^st topic is not completely finished.
The error message always remains when I plot only one data. I added the .value “extension” in the code but I still have the error when I plot one line (broken link).
I don’t know if the sample code I posted previously should be rearranged?.
Thanks

Jon Peltier · Jan 26, 2014

I bet the error occurs because your Names define a range based on the COUNTA, and if this results in a range with no rows, Excel protests.

Instead of clearing the range and pasting one cell at a time (which also might become tedious for whoever is waiting...), put the listbox values into an array, and dump it all at once.

Code:

Private Sub CommandPaste_Click()
    Dim j As Long
    Dim Line as Long
    Dim vOutput As Variant
    With Sheets("Sheet")
        Line = .Range("D65536").End(xlUp).Row
        Redim vOutput(0 To Me.ListBox1.ListCount - 1, 2 To 3)
        For j = 0 To Me.ListBox1.ListCount - 1
            vOutput(j, 2) = CDate(Me.ListBox1.List(j, 2))
            vOutput(j, 3) = CDbl(Me.ListBox1.List(j, 3))
        Next j
        .Range("A3").Resize(Me.ListBox1.ListCount, 2).Value = vOutput
        .Range("A3").Offset(Me.ListBox1.ListCount),Resize(.Rows.Count - Me.ListBox1.ListCount - 3, 2).ClearContents
    End With
End Sub[code]

Untested, but you should get the idea.

JuniorExcel · Jan 26, 2014

Hi
Your code works, (I changed the “coma” into a “dot” after the resize).
I know where the error comes from. Actually my own code even if not adequate works also. In fact the data names start form line 4 and the code past the values at line 3. So I changed the “3” into “4” in the code.
And I also forgot have another dynamic chart which is plotting some values to diagnose the forecast, after fixing the [0] on the data source of this last chart, I didn’t receive an error message (with your code and the previous one).
Thank you very much.
I am closing this post.

Dynamic Chart On VBA

JuniorExcel

New Member

Jon Peltier

MrExcel MVP

Excel Facts

JuniorExcel

New Member

Jon Peltier

MrExcel MVP

JuniorExcel

New Member

Similar threads

Forum statistics

Share this page

Dynamic Chart On VBA

JuniorExcel

New Member

Jon Peltier

MrExcel MVP

Excel Facts

JuniorExcel

New Member

Jon Peltier

MrExcel MVP

JuniorExcel

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock