Change list values using VBA

[zdoitz]

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

[mancubus]

welcome to vbax.

post your workbook pls.

[zdoitz]

Please see workbook attached

welcome to vbax.

post your workbook pls.

[mancubus]

firstly, all


StartColumn = "E"
StartRow = 9
EndColumn = "E"
EndRow = 18

If Intersect(Target, Range(StartColumn & StartRow & ":" & EndColumn & EndRow)) Is Nothing Then



equal to


If Intersect(Target, Range("E9:E18"))Is Nothing Then

avoid unnecessary variable declarations and assignments.


in your file, E9:E18 range contains 'Low Hours' values which are not percentages.

please clearly define which cells will sum up to %100 and which data validation cells will be effected by this change.


PS: Worksheet_Change event is your thing here.