PDA

View Full Version : chart update problem help!!



Markyr
07-31-2008, 07:06 AM
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
07-31-2008, 07:37 AM
Can you post the workbook?

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

Markyr
07-31-2008, 07:43 AM
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
07-31-2008, 07:45 AM
Still don't get it. How do you enter data into charts? And without the workbook, it is very difficult to envisage.

Markyr
07-31-2008, 08:01 AM
would you like me to send you the file?? ....is this allowed here??

Markyr
07-31-2008, 08:02 AM
only 300kb

Bob Phillips
07-31-2008, 09:12 AM
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
07-31-2008, 09:35 AM
cant see the advanced menu

Markyr
07-31-2008, 09:39 AM
file added

Bob Phillips
07-31-2008, 11:11 AM
What do I need to do to generate the error?

Markyr
07-31-2008, 11:16 AM
type in a value into the data sheet to alter any of the column graphs and you will receive the error message!!

Bob Phillips
07-31-2008, 11:21 AM
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
07-31-2008, 11:22 AM
is that the answer??

Bob Phillips
07-31-2008, 11:23 AM
Yep!

Markyr
07-31-2008, 11:28 AM
so I just need to copy and paste the vba into the vba editor, sorry I am new to this!!

Bob Phillips
07-31-2008, 11:31 AM
Yes, just overtype what you copied from earlier.

Markyr
07-31-2008, 11:36 AM
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
07-31-2008, 02:15 PM
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
07-31-2008, 11:08 PM
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
08-01-2008, 01:11 AM
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
08-01-2008, 01:45 AM
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
08-01-2008, 02:13 AM
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
08-04-2008, 12:19 AM
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
08-04-2008, 12:32 AM
Could you just add a Data Validation with Any Value and an input message?

Bob Phillips
08-04-2008, 12:32 AM
BTW, where has the file gone?

Markyr
08-05-2008, 02:24 AM
sorry, did you want to veiw the file again??

Bob Phillips
08-05-2008, 02:32 AM
Yes, so as to be able to envisage what you are now asking.

Markyr
08-05-2008, 02:33 AM
file will be uploaded shortly,

Markyr
08-05-2008, 02:39 AM
Yes, so as to be able to envisage what you are now asking.

Markyr
08-05-2008, 02:40 AM
done!!

Markyr
08-05-2008, 02:49 AM
If you can show me how to make the appropriate changes or add them to the example, that would be of great assistance.

Markyr
08-05-2008, 09:32 AM
Hello Xld, did you get the file????