zdoitz
03-10-2016, 11:04 AM
I am facing an issue trying to use values from a list using data validation (drop down list). I am trying to allocate percentages to resources for tasks the sum should always be 100%. the options for %'s to be used are (25,50,75,100) If I choose 100 for one of the resources I want the drop down list to automatically update with no more selections because now the total is 100%. I I choose 75% then the only option left should be 25% and if I choose 50% the options remaining should be 25%, 25% and if I choose 25% then the options should be 25%,50% and 75% but then if I pick 25% again then the remaining options should be 25% and 50% ... I hope this explains the option selection piece. I have VBA code but it does not seem to work. I attached a link to an image of what I am trying to accomplish with rows, columns etc... 15605
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
StartColumn = "E"
StartRow = 9
EndColumn = "E"
EndRow = 18
If Intersect(Target, Range(StartColumn & StartRow & ":" & EndColumn & EndRow)) Is Nothing Then
Exit Sub
Else
xRow = Target.Row
SumPercent = WorksheetFunction.Sum(Range(StartColumn & xRow & ":" & EndColumn & xRow))
Select Case SumPercent
Case Is = 0
List = "25,50,75,100"
Case Is = 25
List = "25,50,75"
Case Is = 50
List = "25,50"
Case Is = 75
List = "25"
Case Is = 100
List = " "
End Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=List
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
StartColumn = "E"
StartRow = 9
EndColumn = "E"
EndRow = 18
If Intersect(Target, Range(StartColumn & StartRow & ":" & EndColumn & EndRow)) Is Nothing Then
Exit Sub
Else
xRow = Target.Row
SumPercent = WorksheetFunction.Sum(Range(StartColumn & xRow & ":" & EndColumn & xRow))
Select Case SumPercent
Case Is = 0
List = "25,50,75,100"
Case Is = 25
List = "25,50,75"
Case Is = 50
List = "25,50"
Case Is = 75
List = "25"
Case Is = 100
List = " "
End Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=List
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End If
End Sub