onClick action from cell selection

[Beanymonster]

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

 

[Tom Ogilvy]

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

 

[Beanymonster]

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 Data>Validation 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

 

[Tom Ogilvy]

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.

 

[Beanymonster]

thank you - very much appreciated.


: