onClick action from cell selection
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 |
onClick action from cell selection
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/ |
onClick action from cell selection
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 |
onClick action from cell selection
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/ |
onClick action from cell selection
|
All times are GMT +1. The time now is 04:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com