Using variables to place the chart on the spreadsheet

[bmajor]

Hi,

I draw multiple charts on a spreadsheet.

for placing them I use this code: Set rng: Activesheet.Range(A100:N125) that draws me a 25-row high chart to A 100 to N125, working fine

when not all the charts are required, I want to be able to select them and place them automatically in the next empty place.

I created a checkbox, where users can select which chart to display, and the related excel sheet calculates the A and N positions of the chart.
I created variables and made this code:

Set rng: Activesheet.Range("ChartFirstRow:ChartLastRow")

apparently, the variables are working because with SHIFT+F9 I can see the right value from the table, yet VN gives 1004 run-time error
Application-defined or object-defined error.

the chart is drawn, but with the wrong size and wrong location...


any idea how to fix it?

 

[Anthony47]

Sorry, but with such level of details you should ask on thesorcer.com forum...
In general you can set the position of a chart using, for example:

With Sheets("SheetName")
    .ChartObjects(1).Top = .Range("H10").Top
    .ChartObjects(1).Left = .Range("H10").Left
End With

Try adapting the concept to your situation, or please give us some "additional" information on your case

Bye

 

[RoryA]

If those are variables, it should be:

Set rng = Activesheet.Range(ChartFirstRow & ":" & ChartLastRow)

 

[bmajor]

Thanks,

Roy, that didn't work.

I found a workaround.


I have ChartFirstRow as a sting variable still, for the top left corner.

then I set the width and height of the chart, so the next chart could be placed properly after that.

And apparently, VBA accepts single cell reference as a variable so this one

Set rng=ActiveSeet.Range(ChartFirstRow)

seems working.

The full code for the chart positioning is like this now:

 Dim rng As Range
    Set cht = ActiveChart.Parent
    
Set rng = ActiveSheet.Range(ChartFirstRow)  ' I tried different ways to bring here both variables without success. Breaking the address to a column letter "A" & CahartFirstRow: "N" & ChartLastRow and any permutation of those were unsuccessful


    cht.Left = rng.Left
    cht.Width = 1080
    cht.Top = rng.Top
    cht.Height = 345
       
    ActiveChart.Legend.Select
    Selection.Position = xlBottom
    ActiveChart.HasLegend = True
    
    ActiveSheet.Select
    
    Range("A1").Select

And the original one is this

 Dim rng As Range
    Set cht = ActiveChart.Parent
    Set rng = ActiveSheet.Range("E2:Q30")   this part 
    cht.Left = rng.Left
    cht.Width = rng.Width
    cht.Top = rng.Top
    cht.Height = rng.Height
       
    ActiveChart.Legend.Select
    Selection.Position = xlBottom
    ActiveChart.HasLegend = True
    
    ActiveSheet.Select
    
    Range("A1").Select