Working with 3 sheets - syntax to specify the sheet that holds the chart data and format data

nicksoph

Board Regular
Joined
Jun 13, 2009
Messages
57
Hi

I have a workbook which has 3 sheets;
sheet 1 holds the data,
sheet 2 has only one chart which is a smoothed line scatter chart based on the data in sheet 1, and
sheet 3 has three columns which specify RGB values, which I would like to apply to the lines between the the scatter points in sheet 2 so that Row 1's RGB values are used to colour the line between Points 1 and 2, row 2's between points 2 and 3....

I have used the following code to do something similar when the data, chart and colours were all on 1 sheet but am unable to find the syntax for specifying the sheets/chart.

I would like to be able to embed the code in a button on sheet 3 so on clicking it the colour formatting was applied and wonder if someone might oblige with some direction on working with multiple sheets.

Thanks
nicksoph

Code:
Sub colplot()


Dim i As Double, plotcounter As Double


plotcounter = 1 ' The number of the series to plot
       '
       For i = 1 To 20 ' first 20 rows in sheet
       ActiveChart.SeriesCollection(plotcounter).Points(i).Border.Color = RGB(Cells(i, 3), Cells(i, 4), Cells(i, 5))
       
       Next i
End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try (untested):

Code:
Sub colplot()
    Dim i As Double, plotcounter As Double
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet3")
    plotcounter = 1 ' The number of the series to plot
'
    For i = 1 To 20 ' first 20 rows in sheet
        Worksheets("Sheet2").ChartObjects(1).Chart.SeriesCollection(plotcounter).Points(i).Border.Color = RGB(ws.Cells(i, 3), ws.Cells(i, 4), ws.Cells(i, 5))
    Next i
End Sub
 
Upvote 0
Hi and thanks for the quick reply - just tried and it falls over immediately on the line;
Set ws = Worksheets("Sheet9")
with the error - Runtime error '9': Subscript out of range.

I simplified my description earlier and think that may have been a fault - the workbook actually has 10 sheets which each have only one chart on - several sheets have been deleted but the numbers of the sheets of interest and their tab names as shown in the project window are as follows

1. Sheet1 (Calcs) - the data
2. Sheet16 (1 & 2) - the plot
3. Sheet9 (gradient) - the colours

Translating your answer to the actual sheets being used I put the following into the ThisWorkbook section of the VBA project in the workbook;



Code:
Sub colplot() 
Dim i As Double
Dim plotcounter As Double 

Dim ws As Worksheet 

 Set ws = Worksheets("Sheet9")    
plotcounter = 1 ' The number of the series to plot' 
   For i = 1 To 20 ' first 20 rows in sheet
        Worksheets("Sheet16").ChartObjects(1).Chart.SeriesCollection(plotcounter).Points(i).Border.Color = RGB(ws.Cells(i, 3), ws.Cells(i, 4), ws.Cells(i, 5)) 
   Next i

End Sub

I am not any sort of coder so may be doing something wrong rather than there being any error in the code. Any advice appreciated
 
Last edited:
Upvote 0
I did try that but it came back with - Runtime error 13: Type mismatch on - Set ws = Worksheets("gradient")
 
Upvote 0
Hi Andrew
think it was user error - I put an S on the Dim statement - Dim ws as 'Worksheets' rather than 'Worksheet'. Sorry to have wasted your time and thank you for the code. It works smashingly.

Many thanks
nick
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top