Go To Date Function
Hello,
I was wondering if anyone could help me with adding a 'Go To Date' Button to my sheet.
I have tried a couple of things with a userform (calendar) and Match, but it wouldn't select it.
Or a way to group the data per month without loosing the graphs, however, the data in the rows 3:7 have to remain (as they are copied by selection from a different sheet)
Any suggestions are welcome.
Many thanks!
Marianne Testfile.xlsx
You need to hook into chart events. This is a little more tricky as you have chart objects, so a class is required.
I have altered the second charts data range and the dates and values ranges was out of sync.
OMG - it's magical - I'm loving...!!!!
But can't get it working in my sheet...again... I've replaces the charts and rectangle numbers....
I get following message:
Compile error: User-defined type not defined.
And then it's highlighting the line in the Module
m_clsChtEvt (1 To 2) As CChtEvt
What am I missing?
Did you copy across ALL the code in the standard code module, thisworkbook object and CChtEvt class?
At the top of the standard code is a declaration line
Please Login or Register to view this content.
Yes I did copy everything.
I have the declaration line as well...?
Here's my file again. What did I do wrong?
Cash Forecast Accuracy test.xlsm
You need not rename the class module from Class1 to CChtEvt
Also the name of the rectangle to activate events is "Rectangle 9"
testversion1.xlsm
Hi Andy,
Sorry for the late reply.
The error message is gone, but it's still not working. The names of the charts and shape should be fine now.
Furthermore, when I have an empty date field (due to e.g. holiday) in the new month than he wouldn't update the 3 months rule (that you have added in row 8) correctly. I understand why, but I don't know how to solve this.
I have in the sheet from where it picks up the information for the chart following rule:
=IFERROR(VLOOKUP(BT$1;'1086'!$F:$J;2;FALSE);"")
18-Jun-12 17-06-00.png
This is to display an empty field when there is no data rather than the error message.
Is there a solution or would I have to update the empty fields manually with e.g. 0.001?
Thanks again so much!
The %Variance chart errors because the series data source is not based on the same information as the chart labels.
You need to add another named range, similar to CHT_DATA2, and use that as the series data source. You should then be able to click the line and get taken to corresponding cell.
Hi Andy,
So the error is gone, but interactive chart is not working...
I wonder what I'm doing wrong...?
Thank you!
Cash Forecast Accuracy v2.xlsm
You have added the new named range but you are not using it in the chart.
The variance series still has this formula
=SERIES(Dashboard!$B$7,'Cash Forecast Accuracy v2.xlsm'!CHT_LABEL,Dashboard!$AU$7:$DF$7,1)
You need to replace the fixed range reference with the new named range.
OK, that's working now - I have 2 more problems...
The text in the button doesn't change to Enable or disable
And I get a 'Microsoft Office Excel has stopped working' message... after clicking twice on the charts...
What could be the cause of this?
Cash Forecast Accuracy.xlsm
Hi Andy,
I solve the Stopped working message...
But I still can't disable the interactive chart...?
In the file you posted the shape is assigned to a macro in a different workbook.
'C:\Users\mhabitzl\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5\F1TV6V0S\Cash Forecast Accuracy test.xlsm'!ChartEvents
When I assign it to the ChartEvents routine in the workbook it functions as expected.
Excellent - yes, now it's working... !
If you click too quickly around in the sheet I still get the 'Microsoft Office Excel has stopped working' message...
I guess there isn't anything I can do about it, right? (Especially because I don't know what is causing the error message)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks