VBA Excel: Where to place code for Validation List selection. 1

[Wozza]

Hi Guys,
I have set up a Data Validation to reference a list.
I would like some code to kick in once I have selected a value from the Validation's drop-down list.
I am not too sure where to place the code...

Any thoughts / suggestions will be very appreciated!!

Warren Bennet
Wannabe Programmer.
Check out my other vocation;

http://www.aloe2happiness.co.uk

 

[Chance1234]

place it on the worksheet, use the target to reference the cell where your user is selecting the list.



Filmmaker, gentleman and Ambasador for London to the sticks.

http://WWW.5YLAC.S5.COM

 

[SkipVought]

Warren,

I would like some code to kick in once I have selected a value from the Validation's drop-down list.

There are events that can be trapped by Excel VBA -- worksheet change selection, worksheet change, worksheet activate etc. Right click the sheet tab and select View Code to activate the VBA Editor and display the Code Sheet for the worksheet.

In the Object Box select Workbook

In the Procedure Box select Change

Write your code in...

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

which will run every time a selection is made from a Data/Validation List.





Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[VBAjedi]

The Change event is a good place for your code, as Skip suggested. Just be aware that the Change event fires anytime any cell on the sheet is changed. To only run your code when a particular cell/range of cells is changed, you can start your code with something like this:

Dim c As Range, WatchRange As Range
Set WatchRange = Range("A5:A10") ' can be a single cell as well
Set c = Intersect(Target.Cells(1,1), WatchRange) 
' Target is the cell or group of cells that 
' triggered the Change event. It's set 
' automatically by Excel.
If Not c is Nothing Then ' The top-left cell of
' Target is in WatchRange
   ' put your code in here
End If

Hope you find that helpful!


VBAjedi

 

[Wozza]

Guys, thanks for all your help - it is much appreciated.
'Change' now seems so obvious! )

VBAjedi - thanks for the extra tip re. 'WatchRange'
I will add this in to my code, as I am experiencing exactly what you have warned - it is mightily frustrating!

Warren Bennet
Wannabe Programmer.
Check out my other vocation;

http://www.aloe2happiness.co.uk