Priority Graphic Selection and Reset

Hello Bright,

run that past me again:

>>1.) The balls should be selected from a validation list (cell B2) once selected, they should appear in a "active" list (below cell B16).

So, user selects a value in B2. That value should be copied to B16. So far so good.

User selects another value in B2. That value should be
- copied to B17, i.e. the next empty row below B16 ??
- something else?

User keeps selecting values, and they all end up in one list starting from B16 until user selects "Reset All"? User may not select a value more than once?

>>2.) The user should be able to then prioritize any of the selected active choices through some sort of click or button selection

What will that prioritization accomplish? Is it about the order of the data series in the chart?

>>... and the use of the sliders on each particular selection.

I assume the sliders will be used to set the X, Y and bubble size values for the chart? Where do you want to store that data? In the table starting in column H? Or do you want to start another table for the chart data?

We'll need to break this up into chunks, because there are a few complexities involved.

So, let's start with 1.) and where you want the chart data to go. Once we have that in place, we can tackle 2.)

Using conventional formulas, it is possible to reduce the drop-down list by the elements that have already been selected, so they can't be selected again. Combined with a Worksheet_Change event, each selected element can be placed in B16 and following, until a reset command is issued.

I'd hate to muck around with the existing code before we have defined the parameters, though.

Re 2.), prioritizing could happen with a helper column where the user enters the numbers 1-xx against each item in B16 and below, clicks a button and the whole sheebang will be sorted according to the numbering. (yes, it's possible to get more fancy, but it would involve a lot more buttons for up/down and a lot more macro code).

Re 2a.) collecting the X, Y and Z for each item in B16 and below would be another procedure, which I have not quite figured out yet, since it also depends a bit on the clarifications re 1.)

Looking forward to your reply.

cheers, teylyn

>>1.) The balls should be selected from a validation list (cell B2) once selected, they should appear in a "active" list (below cell B16).

YES ( I am open to other ways to "select" from the validation list...this is but one.....and what I like about it is that you probably won't select more then 6)

User selects another value in B2. That value should be copied to B17, i.e. the next empty row below B16 ??

YES

User keeps selecting values, and they all end up in one list starting from B16 until user selects "Reset All"? User may not select a value more than once?

No.  A user can select as many values as they want but may elect to select some subset of the entire list.

YES.  A user MAY NOT select a value more then once.

>>2.) The user should be able to then prioritize any of the selected active choices through some sort of click or button selection

I SHOULD BE MORE CLEAR ON THIS POINT. By selecting a particular choice it appears on the list (below B16).  Since you only have one set of sliders, you must specify which choice you are operating on.  I do not know what visual should be used if any but you have to have a way to use the sliders against the choice.

I assume the sliders will be used to set the X, Y and bubble size values for the chart? Where do you want to store that data? In the table starting in column H? Or do you want to start another table for the chart data?

I had two thoughts on this.   1.) The data could be stored anywhere on the WS because I'm only going to show the graphic and the list.  It seems smart to always have the data and the graphic on the same WS in the event you want to use it again in another WB later.  or 2.) The data could be stored in the Calc_Engine where it really belongs.  YOUR CHOICE.

Thank you for asking questions to clarify.  Sometimes these complex charts require itteration but in all cases it's best to flush out the design before going at it.

B.

OK, got it.

Next: the current chart source table has a whole ballpark of things going on re High/Low and bubble color in columns K, L and M.  I haven't quite figured out if that really is reflected in the actual bubble colors or not. If so, do you want to retain that functionality? In that case, it would make sense to store (or relate) the chart source to those columns and leverage off the existing mechanisms (if any).

In any case, I'll need to catch some ZZZzzzz for the next few hours and will have a look at this tomorrow (NZ time)

cheers, teylyn

Teylyn,

I know there are many formulas in the model to the right of the graphic.  I think they were originally there because I had a static list that was being selected and the formulas helped drive the graphic.  You are welcome to do what you want with "the ballpark"; clean and simple wins the day!

Thank you,

B.

Bright,

have a play with the attached.

Click Reset All to start with a blank slate.

Select outcomes from the yellow drop down and see them appear in B16 and below.
Note that once an outcome appears in B16 and below, it is no longer available in the drop-down.

Select any populated cell in B16 and below and use the sliders to change the values.
Use the "Set values" button to commit the changes to the chart.

Every time "Set Values" is clicked, the macro to color the bubbles will fire, leveraging off the formulas you already had in place.

When switching selection between cells B16 and below, the sliders will reflect the current values for the selected outcome. When clicking somewhere else, the sliders will show the values for the most recently selected outcome and will list that in B12.

You can always manually change the values in the chart source table in columns H to J. When the outcome is next selected in column B, the sliders will reflect these changes.

I added a new standard module and added code to the Priorities sheet module. I've commented out code that got in my way, but I did not delete anything.

cheers, teylyn
SliderGraphmultiplevars-r7-teyly.xlsm

Teylyn,

This is excellent!  I tested it and it works very well for what I'm trying to accomplish.  Just one or three minor tweeks;

Is there a way where the cursor and active outcome you are modifying will stay on THAT OUTCOME until you move the cursor?  Right now you have to reposition it each time you "fire" the new values.  

Size of Investment needs to read opposite of what it reads now.  In other words, the larger the investment, the smaller the size of the ball (less attractive if it costs more).

How do I get the Outcome Name to display either inside or beside each ball?


Many "thanks"....... this is very good work!

B.

Hi again,

see attached.


Yes. After clicking the "Set Values" button, the cell focus will now return to the previously selected outcome cell.


The slider value will now be reversed when copied to the chart source table, i.e. a slider value of 90 will be 10 in the table (small bubble size) and a table value of 80 (large bubble size) will become 20 in the slider when the outcome cell is selected, calculated with the formula 100 - valueX


I added the series name as a data label to each bubble. For ease of reference, it is centered on the bubble, otherwise, it may not immediately be clear what bubble it belongs to.

Note: I'm at home now, working in some obscure version of Excel that may (or may not) produce the same results in Excel 2010. If you find something amiss, please pipe up and I'll fire up the other laptop.

cheers, teylyn
SliderGraphmultiplevars-r7-teyly.xlsm

Teylyn,

I have racked my brain for 3 hours on integrating the table & Graphic into my master WS, and I need to see if you could take a peek at it to see where I have failed.  I think I have narrowed down the problem.

1.) I have checked all Range names and have matched them  up; item for item.
2.) I have copied over the two tabs and insured they are the same (Calc_Engine is now CA_Calc_Engine and Priorities didn't change)
3.) I have copied over the two modules (Now Module 10 and 11) and made the slight adjustments to reference the correct WSs for the slider code.
4.) The Reset Macro should probably have a WS reference.

I have mocked up the integrated WB for you to take a quick look at.  The "table" in the calculations section begins in O113 (CA_CALC_Engine).

I think I've seen this problem before.  When you select the "Available" name range, I get a different response to what you have although the formulas are exactly the same!  And it is in this column where, while the formulas match up; I'm not getting any of the input data/items that would display in the DV Box on the Priorities tab.  Here is the formula where my efforts went "dead".

=IFERROR(INDEX(Original,SMALL(unused,ROW(O113))),0)

Please take a look..... honestly, I wouldn't ask you if I hadn't spent the past 3 hours trying to trouble shoot the move over from your code to the master WB.

Thank you,

B.
Copy-of-Graphic-Problem-with-Wor.xlsm

As I work this back through; it either has something to do with the range name "Available" -- however when I go back to the column representing the defined "range name" it is blank so I"m back to the formula above not populating correctly.

B.

Teylyn,

You are amazing!  However, I'm still doing something incredibly stupid here.  I copied in the formula and it now is showing me the formula instead of calculating the result.
Something-Wrong.png

Teylyn,

After toubleshooting; got it!

Thank you!

B.

You really are the best with graphics and formulas........... much thanks; if I could give you more then 500 points, I would!

B.

Teylyn,

When I select a Outcome, and then hit the value button, I get an error on this line:

   .Interior.Color = Range(Range("BallColor_Addresses").Cells(1, 1).Offset(myInitiativeCounter, 0)).Interior.Color

I get it on your copy as well.  Can you take a quick look?

B.

Wait.... I'm making progress!!!!

Sorry for my silence. I was offline for the last few hours. How are you getting on ?

No worries....been having fun with the graphic.  

Specifically, if I want to change the Cost (3rd slider) back to -0- (not 100) on the slider, however, have it behave in a manner that the higher the number, the smaller the ball, how exactly do I do that?

B.

The slider can only have a small number as a minimum and a larger number as a maximum value. No way around that.

In order to invert the value, you need to involve some calculation in the macro that writes the slider value into the target table cell, and again in the macro that reads the table cell into the slider linked cell when the outcome is selected.

With a slider value from 0 - 100 the formula is easy, i.e. 100 - sliderValue (see the macros in the attachment to post  37721871.

1.) To write the slider value to the cell I used this code in Module 11 in your attachment:

Sub SetValues()
Dim cel As Range
Dim lRow As Integer

[ .. several lines of code in between ]

    Cells(lRow, "J") = 100 - [SizeInv_Slider].Value

[ .. several lines of code in between ]
end sub

Select allOpen in new window

2.) To read the table value back into the cell linked to the slider there is code in the Sheet module of the Priorities Sheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' When an outcome is selected, transfer the values from the chart source table
' to the slider fields

[ .. several lines of code in between ]

        [SizeInv_Slider].Value = 100 - Cells(lRow, "J")

[ .. several lines of code in between ]
end sub

Select allOpen in new window

If you assign different min/max values to the slider, that formula needs to be adjusted.

After testing this; there might be some slight error checking that needs to happen.  For example, if you move the first slider; then try to set values, you get a debug message on line:

  .Interior.Color = Range(Range("BallColor_Addresses").Cells(1, 1).Offset(myInitiativeCounter, 0)).Interior.Color

Additionally, if you don't move the 3rd slider on "Cost", you do not get a graphic..........

Those are two of the problems a user might have.

B.

Hmm.

Yes, I see that. Maybe the macro needs to check that the linked cells for the sliders are not empty or show a value of at least 1.

Give me a few minutes.

cheers, teylyn

Try this in Module 11 (replace the existing sub called SetValues)

Sub SetValues()
Dim cel As Range
Dim lRow As Integer

If Not TypeName(Selection) = "Range" Then
    MsgBox "Please click one of the selected outcomes before clicking 'Set values'!"
    Exit Sub
End If

If Selection.Row < 16 Or Selection.Row > 29 Then
    MsgBox "Please click one of the selected outcomes before clicking 'Set values'!"
    Exit Sub
End If
' only run this code when all three slider link cells have values
If WorksheetFunction.Count([Cmplx_Slider], [BVCost_Slider], [SizeInv_Slider]) <> 3 Then
    MsgBox "Please select a value for each of the three sliders."
    Exit Sub
End If
' find the row of the selected cells in the chart source table
    lRow = Selection.Row - 12
' transfer the slider values to the chart source table
    Cells(lRow, "H") = [Cmplx_Slider].Value
    Cells(lRow, "I") = [BVCost_Slider].Value
    Cells(lRow, "J") = 100 - [SizeInv_Slider].Value
    UpdateChartBallColors
    Range("B" & lRow + 12).Select
End Sub

Select allOpen in new window

I get some funny behaviour outside of this immediate scope, because some things that your file needs are not found in the places it expects them to, but the macro in question is not affected, I think.

That seems to work very well!  Where do I look for changing the Cost slider back to -0- instead of 100 at reset?

Thanks for sticking with this.  I'll stop asking you questions.  I am very pleased with this graphic!

B.

>> Where do I look for changing the Cost slider back to -0- instead of 100 at reset?

I can't answer that, since the "Reset All" button is linked to a macro that is not included in the file attached above. If you can post the code that  sits behind that button, I'd be happy to help.

Maybe this could be a new question, though, since it is not directly related to this charting issue, and you would get the chance to trial the expertise of other experts who won't go to bed in ten minutes.

cheers,

Will do!  Thank you.  And good night.