chart update problem help!!

[Markyr]

Can someone help!!!:dunno

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!!!

:banghead:

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]

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

[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??

:friends:

[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.

[Markyr]

Thanks for the feedback, Not being an accountant, analyst or programmer, I think I need an excel mentor!!!! .

I used the speedo style chart to prove to myself that I could do it, (pretty akward to do for the average excel user).


No doubt i'll be back to pester you again for some answers to vba & excel.

Thanks for all your help,


Mark

[Bob Phillips]

Let's hope so, it is a good forum, and there are some smart people here, so you should get answers 99% of the time.

[Markyr]

Hello XLD,

Told you I'd be back again!!

Following on with the dashboard, I would like to be able to link a text box showing, a period commentary.

For example click into a dropdown menu located on the dashboard, scroll to the appropriate period,(1-13) and a text box will appear with the commentary for that period. Is that a job for vba?? or can it be easily done within excel?? ......

Hope you can help,

Thanks again,

Mark

[Bob Phillips]

Could you just add a Data Validation with Any Value and an input message?

[Bob Phillips]

BTW, where has the file gone?

[Markyr]

sorry, did you want to veiw the file again??

[Bob Phillips]

Yes, so as to be able to envisage what you are now asking.

[Markyr]

file will be uploaded shortly,

[Markyr]

Yes, so as to be able to envisage what you are now asking.

[Markyr]

done!!

[Markyr]

If you can show me how to make the appropriate changes or add them to the example, that would be of great assistance.

[Markyr]

Hello Xld, did you get the file????