Solved

Excel: Duplication of sheets with formulas set to manipulate charts

Posted on 2011-02-13
11
230 Views
Last Modified: 2012-06-27
Experts,

I have been working on a dashboard, that the functionality is developed.  However, I would now like to continue to modify this new code to other sheets in the spreadsheet.

I have been working closely with an expert who has helped me code.  I am looking to have a better understanding of the code. See related post for spreadsheet.

thanks!
0
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34885335
Ok - here it is for the next sheet. and some explanation on how it all works:

You only needed this change.  Executive Rollup looks at the same ranges as Summary Dashboard, for the Listbox2 control

That would be - Summary_Delta_Range:

Private Sub ListBox2_LostFocus()

    Call UpdateChart_ArrayOnLBLostFocus(ListBox2, "Summary_Delta_Range", "Executive Rollup Data", "Executive Rollup", "Chart 238", True)
    Call UpdateChart_ReviseTitle(ListBox2, "Summary_Delta_Range", "Executive Rollup Data", "Executive Rollup", "Chart 238")
   
End Sub

Reflect a bit on how all this works, as you move forward...

For you need an UpdateChart_ArrayOnLBLostFocus AND an UpdateChart_ReviseTitle on
the single listbox with columns (recall also that the columnar listbox needs to do
vlookup on the parsed out key, so in this instance, that's still Summary_Delta_Range.

If you were to do this on other charts with different data sheets, youd need to set the
data up JUST LIKE the EXECUTIVE ROLLUP DATA sheet - the keys in the first column at the top,
the columns with different key in the first column on the bottom dataset.

The data ranges set out across the top

and referenced in the bottom.

The option buttons copied from one sheet to another

The option button code copied from a working sheet to the new sheet.

Note I've updated option buttons 1-6 now.

See attached:

Dave

PS - at some point, you're going to wonder why when you hit the arrow button, the chart doesn't open when
you click it after having done anything but selected something to chart.
and that's because the listbox lostfocus didn't fire.  If you make a command button macro for each one of these
then you can call the Listbox1_Lostfocus routine.  You can't run that routine OUTSIDE of the SHEET CODEPAGE.

Don't worry, you can make the command button transparent with your arrow "Update" icon in the background.

If you need help, ask another question and I'll walk you through it more detailed than this.


Let's make sure all your questions on what's been done have been answered (before moving to the next problem) then close out.  That way, you've got a documented understanding.

Dave
NOC-Reports-r17.xlsm
0
 

Author Comment

by:Maliki Hassani
ID: 34885367
Beautifully worded.  I understood everything.  I will give it a try!  It fells good when you understand and can teach someone based off of your own knowledge.  Thank you again!!
0
 

Author Comment

by:Maliki Hassani
ID: 34893161
Dave,

FYI,

I have 3 sheets functional!  It is looking really good!  Thank you so much..  I do have a several small questions.. Next question is if I can upload this to sharepoint and have full functionality..  One sec!
0
RoboForm Secure Password Management System

RoboForm Everywhere - Superb Browser Support
Windows / Apple / IOS / Android / Linux / Chrome OS
Use different complex passwords everywhere
Best Secure Password Management by far
Synchronize all of your devices instantly
Safe, Secure & Highly Recommended!

 

Author Closing Comment

by:Maliki Hassani
ID: 34893168
As always the best help...  You are much appreciated!
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34893261
As there are no links, you can upload to Sharepoint, if other users are downloading to function.  Not sure if you convert to HTML (haven't messed with that much) how it will work but would like to know.

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34893278
Okay..  Maybe I need to ask this question in the sharepoint section, not in excel.  Thanks..  I do have a question about the following:

PS - at some point, you're going to wonder why when you hit the arrow button, the chart doesn't open when
you click it after having done anything but selected something to chart.

--- I am experiencing that issue and would like to know more..  I will post a question about this, okay?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34893330
ok, but read the next sentence as it tells you why.

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34893344
Okay...  Maybe I don't need the question then..  
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34893347
Just give it a shot and if you have problems - post away.
0
 

Author Comment

by:Maliki Hassani
ID: 34893349
I do have 2 post that are related already  if you want to give them a try..
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34893386
I only saw one and responded.  Going to do the valentines thing.

Later,

Dave
0

Featured Post

Increase your protection from Zero Day threats!

Running two Antivirus' is never a good idea.
Taking advantage of Multiple Security layers on the other hand can often save your hide.
See which top notch security software brands have been proven to happily coexist together.
Reduce your chances of becoming a statistic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question