Solved

Excel:  Option buttons to connect to Chart

Posted on 2011-02-13
27
235 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
ID: 34884607
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
ID: 34884620
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
ID: 34884640
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
ID: 34884645
Nope - won't work.  Hold on...
0
 

Author Comment

by:Maliki Hassani
ID: 34884652
Its firing but nothing is changing.. hmm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34884656
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
ID: 34884657
I do believe the names of the buttons had changed .. I could be wrong let me see..
0
 

Author Comment

by:Maliki Hassani
ID: 34884659
Sa-Weet..  Thanks!!
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34884663
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
ID: 34884678
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
ID: 34884680
Works like a charm!!  
0
 

Author Comment

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

Author Comment

by:Maliki Hassani
ID: 34884685
So what if I wanted to fire from both listboxes?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 41

Expert Comment

by:dlmille
ID: 34884698
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
ID: 34884721
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
ID: 34884738
Just noticed ReviseTitle.  Pretty smooth
0
 

Author Comment

by:Maliki Hassani
ID: 34884777
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
ID: 34884781
Are you going to vary date ranges with SUrveillance?
0
 

Author Comment

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

Accepted Solution

by:
dlmille earned 500 total points
ID: 34884790
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
ID: 34884792
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
ID: 34884796
I owed you that one on the last post as I hadn't fully explained it...

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34884805
You are still man!! HA
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34885010
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
ID: 34885050
Roger!
0
 

Author Comment

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

Author Comment

by:Maliki Hassani
ID: 34885312
posting!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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.

861 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

25 Experts available now in Live!

Get 1:1 Help Now