Solved

Excel:  Experiencing loss of command controls when selecting listbox to open chart

Posted on 2011-02-14
6
808 Views
Last Modified: 2012-06-27
Experts:  

I was wondering why this occurs when I hit the arrow button in my dashboard, the chart doesn't open when
I click it.  I was told that the button is not firing but there is a way to call that command and create a transparent box that would go behind the arrow button.  I was hoping someone can explain a bit more on this.  See previous thread to find more that was explained.
 NOC-Reports-r17.xlsm
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
  • 3
  • 3
6 Comments
 

Author Comment

by:Maliki Hassani
ID: 34893369
So I take it that I will need to copy all ListBox1_LostFocus and ListBox2_LostFocus in module1 .  However, being that it was a private function and I have a total of 16 of each lostfocus pointing to different sheets.  I should name the sub 1 - 16 because it wont matter since it is not set to private.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34893382
Ok - you can replicate this.  On Executive Rollup I put a command button on top of your icon on the left, then made it transparent, go into design mode and click on the icon and you'll find the object on top - see properties of that object and that its transparent.  double click on the command button and you'll see the code calls the listbox1_lostfocus() routine and hence udpates the chart.  if it works the way you like, you can then replicate that on top of all the buttons, as there's really no need to do the sheet visible routine you're doing when you hit the icon.  just trying to lose focus by getting out of the listbox.  so having the icon there does that for you and the transparent one on the top forces the routine to run regardless of whether you were in the listbox or not.

If there's nothing selected in the listbox and button is hit, it would be impotent just like it is now.

Dave
NOC-Reports-r18.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34893384
Or just get rid of the Icon and use the command button with a <-> text caption...

Dave
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Maliki Hassani
ID: 34894171
Okay, so I did what you recommend and just got rid of the icon and that works perfectly.  Not sure if you want me to  post another question but I found another issue. lol  When selecting from list2 it will update the chart, which is great.  However, if I directly go and change the to  3 month view it selects whatever is highlighted in list1 as the new chart with shows the 3 month view.  

I understand that the ob are firing ListBox1_LostFocus but it should be ListBox2_LostFocus.  Any thoughts on how to resolve this issue? This seems a liitle bit tricky to fix..  It is like eith you choose to keep it the way it is or remove the command from list2..
NOC-Reports-r18.xlsm
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34895185
Neat how you got a picture on your command button!  I see how you did it in properties...

Not sure what happend to command_button1 (you must have created it then deleted it). You now have _2 and _3 in the sheet.  I renamed to _1 and _2 and updated the code.

I added the PUBLIC variable lastLB (declared in NavigationandZoom Routines module), and set it to the name of any listbox losing focus.  That way it can be used to determine which gets fired (with preference to 1 if both empty) when option buttons are selected.  To keep it simple, I used one variable.

so, when you choose an obutton, on any sheet, if ListBox2 was the last, then that's what will plot (if anything in ListBox2, unless user selects from the list of a listbox...

This just in Executive Rollup, which you should be able to duplicate.

Dave
NOC-Reports-r18.xlsm
0
 

Author Comment

by:Maliki Hassani
ID: 34901222
Worked perfectly!  Just completed the majority of the repetative task.  I added 2 more list box and modified the fire command to trigger.  Thank you!

So I have another question that I am about to post.  This is related to creating a list box that will populate with the total count and average of EACH selected ID#.  I have setup the dashboards with a listbox but just need expert guidance on what is the best way to approach this.  Posting now ith new version update
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to access a o365 sharepoint tem site from o365 mail? 5 25
split data by day 21 35
need 2017 datas one by one using macro 12 38
Lync 2010 Certificate 3 15
Outlook Free & Paid Tools
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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

697 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