Hi All,
In a worksheet I am creating I have a cell which, using a list
contains the options <please select, Yes and No. I would like it tha
if a user selects YES, they are automatically taken to worksheet 3 bu
is they select No, they are taken to worksheet 4. Is that possible?
If it makes any difference, I also have conditional formatting on th
cell selection.

Many thanks in advanc

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

Assume you are talking about Data=Validation using the List option and this
is Excel 2000 or later. Further assume that the cell in question is B9

right click on the sheet tab of the sheet with this cell and select view
code. Paste in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If target.count 1 then exit sub
if target.address = "$B$9" then
if target.Value = "Yes" then
Worksheets(3).Activate
elseif Target.Value = "No" then
Worksheets(4).Activate
end if
End if
End Sub

--
Regards,
Tom Ogilvy


"Beanymonster " wrote in
message ...
Hi All,
In a worksheet I am creating I have a cell which, using a list,
contains the options <please select, Yes and No. I would like it that
if a user selects YES, they are automatically taken to worksheet 3 but
is they select No, they are taken to worksheet 4. Is that possible?
If it makes any difference, I also have conditional formatting on the
cell selection.

Many thanks in advance


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

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 i
column B).

Also, is it possible to change the sheet tab name or does it have t
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

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/

thank you - very much appreciated.


:

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