Private Sub Worksheet_Change(ByVal Target As Range)
If target.count 1 then exit sub
On error resume Next
set rng = Columns(2).SpecialCells(xlCellTypeAllValidation)
ON error goto 0
if rng is nothing then exit sub
if not Intersect(Target, rng) is Nothing then

if target.Validation.Type = xlValidateList then
if target.Value = "Yes" then
Worksheets(3).Activate
elseif Target.Value = "No" then
Worksheets(4).Activate
end if
End if
End if
End Sub

I didn't use sheet tab names. You can use sheet tab names in lieu of the
index numbers I used.

--
Regards,
Tom Ogilvy


"Beanymonster " wrote in
message ...
Hi Tom - excellent - thank you very much. Works great, however....
using your example, is it possible to extend this to work on cells B10,
B11, B12 etc (ie any cell with the DataValidation list option in
column B).

Also, is it possible to change the sheet tab name or does it have to
remain as default name (Sheet3, Sheet4 etc).

Sorry for all the questions, I am a severe newbie at VB stuff

thanks again.


---
Message posted from http://www.ExcelForum.com/