 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2006Hide sheet when changing a cell
Hello, can anybody tell me how to hide a tab when I'm changing a cell. Sample: I have sheet A, B, C, D, E, F. When I put an E in cell B2 from sheet A. then I want to appear sheet E and hide sheet B, C, D and F. Can anybody tell me how to do this? Or which code I have to use? Thanx! "A" Worksheet code: '------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim rWatch As Range Dim sSheet As Object Set rWatch = Range("B2") If Intersect(Target, rWatch) Is Nothing Then Exit Sub For Each sSheet In Sheets If sSheet.Name <> "A" Then sSheet.Visible = sSheet.Name = rWatch.Value End If Next sSheet End Sub '-------------------------------------------------------------- -- HTH -- AP > Hello, can anybody tell me how to hide a tab when I'm changing a cell. > [quoted text clipped - 8 lines] > > Thanx! That didn't work. Also I have more sheets than 6 which I want to show. So if I put in cel B2 an "B" I want to show sheet A, B, G and H. If I put a "C" in cel B2 than I want to show sheet A, C, G and H. If I put a "D" in cel B2 than I want to show sheet A, D, G and H. If I put a "E" in cel B2 than I want to show sheet A, E, G and H. Please help! Greets '-------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim rWatch As Range Dim sSheet As Object Set rWatch = Range("B2") If Intersect(Target, rWatch) Is Nothing Then Exit Sub For Each sSheet In Sheets(Array("B", "C", "D", "E")) sSheet.Visible = sSheet.Name = rWatch.Value Next sSheet End Sub '-------------------------------------------------------------- HTH -- AP > That didn't work. Also I have more sheets than 6 which I want to show. > So if I put in cel B2 an "B" I want to show sheet A, B, G and H. [quoted text clipped - 5 lines] > > Greets This is what I fill in: '-------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim rWatch As Range Dim sSheet As Object Set rWatch = Range("B9") If Intersect(Target, rWatch) Is Nothing Then Exit Sub For Each sSheet In Sheets(Array("TPG Post via CH", "Deutsche Post", "Spring Royal Mail", "Select Mail", "TPG Post via Lettershop", "Sandd")) sSheet.Visible = sSheet.Name = rWatch.Value Next sSheet End Sub '-------------------------------------------------------------- If I fill "Spring Royal Mail" in cell B2 in sheet A it used to be that only sheet Spring Royal Mail apairs. And sheet "H" stay visible. It's not working YET. Greetings Please send me your worbook as attachment at ardus.petus@laposte.net HTH -- AP > This is what I fill in: > [quoted text clipped - 16 lines] > It's not working YET. > Greetings You must use continuation lines _ for lines too long. Here is the correctet workbook: http://cjoint.com/?gjlyLeRBRk Cheers -- AP > Please send me your worbook as attachment at ardus.petus@laposte.net > [quoted text clipped - 22 lines] >> It's not working YET. >> Greetings Thank you so much, Cheerio |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.