Solved

Excel:  Option buttons to connect to Chart

Posted on 2011-02-13
27
234 Views
Last Modified: 2012-05-11
Experts:

I can seem to get my option buttons to connect to my chart 238 on the Executive rollup sheet.  I am not sure what modules have been changed in the previous version that was used to get "Summary Dashboard" chart "Main" to work.  Do I need to rename all the charts in the workbook "Main" that I want to use option buttons for?  I need a step by step process here...  I was hoping to connect the listbox1 and 2 to control the main chart.
I then need to know what needs to be done to get my "Surveillance Dashboard" sheet functional.  Here is the newest version

thanks  NOC-Reports-r15.xlsm
0
Comment
Question by:Maliki Hassani
  • 16
  • 11
27 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
go to summary dashboard codesheet.  See all the option button click routines?  You need routines like those in your Executive tab.  no need to rename charts (that was just to keep your head on with all the numbered stuff, but not necessary - only for longer-term maintenance).

See the summary dashboard codesheet.  go to the bottom where the option buttons are.  you could copy all that to the Executive Rollup codesheet.  then make sure the option buttons have the same name for 1d, 1wk, etc., and the option buttons will fire - otherwise, need to rename the routines to the optionbuttons you have to tie.

Make sense?  Make the attempt and I'll assist, after.

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Gotcha..  I was thinking that I would have to duplicate all the ListBox1_DblClick.  Let me work my magic!! lol  
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Delete option button group on Executive Dashboard.  Go to SUmmary dashboard, copy your option button group onto the Executive Dashboard, then the ob #'s are the same.  When you run the code you copied from Summary dashboard for the option button clicks it should all be linked...
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Nope - won't work.  Hold on...
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Its firing but nothing is changing.. hmm
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I renumbered the option buttons on summary dash board to be 1 to 6 and supporting code.  Its here.

Now, you can copy the option buttons to the Executive Dashboard and the code for option buttons on the summary codepage to the executive dashboard codepage...

I'm having you do it to reinforce what's going on.  Let me know if you need assist.

PS - looking impressive.

see attached for renamed option buttons on summary dashboard with respective code updated.

Dave
NOC-Reports-r16.xlsm
0
 

Author Comment

by:Maliki Hassani
Comment Utility
I do believe the names of the buttons had changed .. I could be wrong let me see..
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Sa-Weet..  Thanks!!
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Oh - and of course the last parameter on ListBox1_LostFocus has to be set to TRUE (tells the routine option buttons are involved so adjust where it looks).

on the Exeuctive Rollup codepage - not the others, till you're ready.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
PS - get rid of all your routines looking at combobox1_change - you no longer have that control.

If you do a Debug Compile, the compiler will tell you all that's wrong from a compile standpoint and I just got that error.  A bunch of code stubs you're no longer using and could create confusion during maintenance or when asking for help.  So delete them...

I got my Executive Rollup to work.  You?

:)

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Works like a charm!!  
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Yes I noticed the combobox routines..  I was like do I need this?  lol
0
 

Author Comment

by:Maliki Hassani
Comment Utility
So what if I wanted to fire from both listboxes?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
dunno - I know what the left one supposed to do.  What's the right one supposed to do?

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Well the only thing that I was think was if a person wanted to see the ones with arrows.  Yes, that is pretty lazy of them not to just look of the ID number in listbox1 but I was thinking it would be cool.

I do have another question that I need to post in reference to moving forward with the other sheet "Surveillance".  I noticed that the graphs are not working because you created a new way of firing.  So let me post that one..
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Just noticed ReviseTitle.  Pretty smooth
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Okay I am was modifying the code to get listbox2 to work. I duplicated the folowing
ListBox2_LostFocus and ListBox2_DblClick and it is only changing the title to the error message "Can't find"
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) ' this will clear listbox selection
Dim lItem As Long

    If ListBox1.ListCount <> 0 Then 'only if something is selected, otherwise, do nothing
        For lItem = 0 To ListBox1.ListCount - 1 'interrogate the listbox

            If ListBox1.Selected(lItem) = True Then
                ListBox1.Selected(lItem) = False
            End If
        Next lItem
    Else
        'do nothing
    End If
End Sub
Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean) ' this will clear listbox selection
Dim lItem As Long

    If ListBox2.ListCount <> 0 Then 'only if something is selected, otherwise, do nothing
        For lItem = 0 To ListBox2.ListCount - 1 'interrogate the listbox

            If ListBox2.Selected(lItem) = True Then
                ListBox2.Selected(lItem) = False
            End If
        Next lItem
    Else
        'do nothing
    End If
End Sub
Private Sub ListBox1_LostFocus() 'when something else on the spreadsheet is selected
    
    Call UpdateChart_ArrayOnLBLostFocus(ListBox1, "Executive_Range", "Executive Rollup Data", "Executive Rollup", "Chart 238", True)

End Sub

Private Sub ListBox2_LostFocus() 'when something else on the spreadsheet is selected
    
    Call UpdateChart_ArrayOnLBLostFocus(ListBox2, "Executive_Range", "Executive Rollup Data", "Executive Rollup", "Chart 238", True)

End Sub



Private Sub Worksheet_Activate()

    Call ZoomRoutine(Range("A1:AD56"), Range("D2:L3"))
    
End Sub

'--------------------- Option Buttons for MAIN chart -------------------------------
'--- obMainChart is set to the number of columns (moving left) from the end of Executive_Range, for plotting the chart.  Use this variable
'--- for all summary sheets that have the same setup as Summary Dashboard (you're only graphing one at a time!)

Private Sub OptionButton1_Click() 'main chart 1 day
    obMainChart = 0
    Call ListBox1_LostFocus 'fire the graph
End Sub

Private Sub OptionButton2_click() 'main chart 1 week
    obMainChart = 1
    Call ListBox1_LostFocus 'fire the graph
End Sub
Private Sub OptionButton3_click() 'main chart 1 month
    obMainChart = 2
    Call ListBox1_LostFocus 'fire the graph
End Sub
Private Sub OptionButton4_click() 'main chart 3 months
    obMainChart = 4
    Call ListBox1_LostFocus 'fire the graph
End Sub
Private Sub OptionButton5_Click() 'main chart 6 months
    obMainChart = 5
    Call ListBox1_LostFocus 'fire the graph
End Sub
Private Sub OptionButton6_Click() 'main chart 12 months
    obMainChart = 6
    Call ListBox1_LostFocus 'fire the graph
End Sub

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Are you going to vary date ranges with SUrveillance?
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Yes, the same design as the executive rollup sheet.. Same exact thing..
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
In the Surveillance codepage just set the last parameter to True.  This was not its intent, but its a patch for now.  As you haven't created all the date ranges for option buttons, its looking three columns back (as a default) and ranges aren't there yet.  So setting to true, sets the offset value to ZERO and uses that (versus changing it to 3 as a default to 2 weeks).

If you HAD the date ranges like Executive data, then the option False would have worked with a 3 column lookback to 2 weeks.

Make sense?

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
I need step out and have my valentines day dinner ..  Be back in 2 hours!!  I will post my next question during my dinner date.. lol
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I owed you that one on the last post as I hadn't fully explained it...

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
You are still man!! HA
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Post your question refer to this - no need to upload the file again, I don't think.  Been thinking on it and think I might be close!

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Roger!
0
 

Author Comment

by:Maliki Hassani
Comment Utility
Can't post on my phone be back home in 45 min..
0
 

Author Comment

by:Maliki Hassani
Comment Utility
posting!!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now