Reputation: 329
I have some code in a spreadsheet which should change the value in a validaiton list on a worksheet called 'analysis'. The value in cell B1 on the analysis list should change to FTE if the value 'Costs' is already selected, else it should select the value 'costs'.
My code below doesn't seem to be changing the value in the validation list, and when I try to record a macro to get an idea of how a macro recording would record the validation change, the recorder doesn't record the validation change. Does anybody have any idea how to select a value from a validation list via VBA?
My current code is below
Sub ChangeValue()
Worksheets("Analysis").Select
With Worksheets("Analysis")
If Range("B1").Value = "Costs" Then
Range("B1").Value = "FTE"
Else
Range("B1").Value = "Costs"
End If
End With
End Sub()
Upvotes: 0
Views: 15364
Reputation: 329
This was a total oversight on my behalf, the code was written as Range rather than .Range. Fix below
Sub ChangeValue()
Worksheets("Analysis").Select
With Worksheets("Analysis")
If .Range("B1").Value = "Costs" Then
.Range("B1").Value = "FTE"
Else
.Range("B1").Value = "Costs"
End If
End With
End Sub()
Upvotes: 2