chart update problem help!!

[Markyr]

Can someone help!!!

I have written this code to update a speedo style chart in my workbook,

I also have a number of other charts on the same sheet, however when I enter data in one of the other charts. I get the following message " runtime error 1004 unable to get the chartobjects property of the worksheet class"

(I am a beginner at writing vba code) and I am reaching for the headache tablets!!!



Copy of code show below

WORKBOOK SHEETCHANGE

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False

ActiveSheet.ChartObjects("speedo").Activate

ActiveChart.ChartGroups(1).FirstSliceAngle = ActiveSheet.Range("d42")

Worksheets("Area Dashboard").Activate

Range("a1").Select

Application.ScreenUpdating = True

[Bob Phillips]

Can you post the workbook?

What do you mean by ... when I enter data in one of the other charts...?

[Markyr]

I have other charts on the same page (non vba), when data is entered into them I get this mesage " runtime error 1004 unable to get the chartobjects property of the worksheet class"

[Bob Phillips]

Still don't get it. How do you enter data into charts? And without the workbook, it is very difficult to envisage.

[Markyr]

would you like me to send you the file?? ....is this allowed here??

[Markyr]

only 300kb

[Bob Phillips]

You can post it here. Click the Go Advanced button, then on the new screen, scroll down a bit and you will see Manage Files, that will allow you to attach a file.

[Markyr]

cant see the advanced menu

[Markyr]

file added

[Bob Phillips]

What do I need to do to generate the error?

[Markyr]

type in a value into the data sheet to alter any of the column graphs and you will receive the error message!!

[Bob Phillips]

[vba]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.ScreenUpdating = False

With Worksheets("Area Dashboard")

.Activate
.ChartObjects("speedo").Activate
ActiveChart.ChartGroups(1).FirstSliceAngle = .Range("D42").Value
.Range("a1").Select
End With

Application.ScreenUpdating = True

End Sub
[/vba]

[Markyr]

is that the answer??

[Bob Phillips]

Yep!

[Markyr]

so I just need to copy and paste the vba into the vba editor, sorry I am new to this!!

[Bob Phillips]

Yes, just overtype what you copied from earlier.

[Markyr]

Thanks everso!!!

It works brilliantly, I bow to your knowledge sir, I am new to this, do you know of any good beginner courses on vba, or did you learn by trial and error??

[Bob Phillips]

I am afraid I am not the person to recommend an approach/methodology to you. I am an old time IT bod, who is so sad that he even bought Apple II's, BBC, early IBM to play with these things in his own time. So when I came to Excel, and much later, to VBA, I was already an old-timer at the programming game. Plus, I used VB befoe VBA, so I had a head-start.

[Markyr]

thanks for your help, I was looking at a career change after 20 years in transport (you think your'e sad!!) what do you think of my 1st attempt at an excel dashboard??

[Bob Phillips]

I think it is good, you have not made the cardinal error of too much extraneous colour, and in general you are using informative charts (not a pie to be seen). I do not like speedo charts though, they lack information, and it can usually conveyed better. Remember, you can use data in a dashboard, it doesn't have to be all gizmos and widgets.