VBA - Select value from Validation List

Question

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()

This is slightly confusing. Why would you change the value to FTE by clicking on the costs in the same cell? or is the list in some other worksheet? Are the above B cells in different sheets (One being "Analysis")? — Siddharth Rout, Commented Nov 10, 2014 at 11:29

PootyToot · Accepted Answer · 2014-11-09 23:56:19Z

3

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()

answered Nov 9, 2014 at 23:56

PootyToot

3492 gold badges10 silver badges28 bronze badges

2

I agree with @TimWilliams comment. There's nothing wrong with your code above since you select the worksheet first. However above code is better and also you can remove the Worksheets("Analysis").Select line.
– L42
Commented Nov 10, 2014 at 1:41

Add a comment |

Collectives™ on Stack Overflow

VBA - Select value from Validation List

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related