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
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
If you assign different min/max values to the slider, that formula needs to be adjusted.
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
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
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.