CODEWITHSUNDEEP
vbavalidationexcel
PootyToot
PootyToot

Reputation: 329

VBA - Select value from Validation List

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

Answers (1)

PootyToot
PootyToot

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

Related Questions