• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

Excel: Option buttons to connect to Chart

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
Maliki Hassani
Asked:
Maliki Hassani
  • 16
  • 11
1 Solution
 
dlmilleCommented:
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
 
Maliki HassaniAuthor Commented:
Gotcha..  I was thinking that I would have to duplicate all the ListBox1_DblClick.  Let me work my magic!! lol  
0
 
dlmilleCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
dlmilleCommented:
Nope - won't work.  Hold on...
0
 
Maliki HassaniAuthor Commented:
Its firing but nothing is changing.. hmm
0
 
dlmilleCommented:
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
 
Maliki HassaniAuthor Commented:
I do believe the names of the buttons had changed .. I could be wrong let me see..
0
 
Maliki HassaniAuthor Commented:
Sa-Weet..  Thanks!!
0
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
Maliki HassaniAuthor Commented:
Works like a charm!!  
0
 
Maliki HassaniAuthor Commented:
Yes I noticed the combobox routines..  I was like do I need this?  lol
0
 
Maliki HassaniAuthor Commented:
So what if I wanted to fire from both listboxes?
0
 
dlmilleCommented:
dunno - I know what the left one supposed to do.  What's the right one supposed to do?

Dave
0
 
Maliki HassaniAuthor Commented:
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
 
Maliki HassaniAuthor Commented:
Just noticed ReviseTitle.  Pretty smooth
0
 
Maliki HassaniAuthor Commented:
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
 
dlmilleCommented:
Are you going to vary date ranges with SUrveillance?
0
 
Maliki HassaniAuthor Commented:
Yes, the same design as the executive rollup sheet.. Same exact thing..
0
 
dlmilleCommented:
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
 
Maliki HassaniAuthor Commented:
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
 
dlmilleCommented:
I owed you that one on the last post as I hadn't fully explained it...

Dave
0
 
Maliki HassaniAuthor Commented:
You are still man!! HA
0
 
dlmilleCommented:
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
 
Maliki HassaniAuthor Commented:
Roger!
0
 
Maliki HassaniAuthor Commented:
Can't post on my phone be back home in 45 min..
0
 
Maliki HassaniAuthor Commented:
posting!!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 16
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now