+ Reply to Thread
Results 1 to 2 of 2

Unhide/hide multiple sheets depending on dropdown list

  1. #1
    Registered User
    Join Date
    02-06-2018
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    3

    Unhide/hide multiple sheets depending on dropdown list

    Hi All,

    I have a spreadsheet with 3 sheets, sheet1, sheet2 and sheet3. I've made a dropdown elsewhere with options 0, 1, 2, 3.

    I want selecting each option to result in the following:
    Select 0: Hide Sheet 1, 2, and 3
    Select 1: Unhide Sheet 1, Hide Sheet 2 and 3
    Select 2: Unhide Sheet 1 and 2, Hide sheet 3
    Select 3: Unhide sheet 1, 2 and 3

    On a similar vein, I'd ideally like the selection to also hide or unhide 3 adjacent rows, aka:
    Select 0: Hide Row 1, 2, and 3
    Select 1: Unhide Row 1, Hide Row 2 and 3
    Select 2: Unhide Row 1 and 2, Hide Row3
    Select 3: Unhide Row 1, 2 and 3
    However, I sadly have no idea how to do this.

    I've tried to do this but I cant seem to be able to get it to work.
    Private Sub Worksheet_Change(ByVal Target As Range)

    If [NoModels] = "0" Then
    Sheets(Array("Model1", "Model2", "Model3")).Visible = False
    End If

    If [NoModels] = "1" Then
    Sheets("Model1").Visible = True
    Sheets(Array("Model2", "Model3")).Visible = False
    End If

    If [NoModels] = "2" Then
    Sheets(Array("Model1", "Model2")).Visible = True
    Sheets("Model3")).Visible = False
    End If

    If [NoModels] = "3" Then
    Sheets(Array("Model1", "Model2", "Model3")).Visible = True
    End If

    End Sub
    Am I at all close?

    If anyone could help me out, and let me know how to adapt this to include rows too, I would be unbelievably grateful.

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Unhide/hide multiple sheets depending on dropdown list

    Try removing the quotes around your numbers, excel will see these as text and not a number with the quotes around them.
    so
    Please Login or Register  to view this content.
    Becomes
    Please Login or Register  to view this content.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Hide and Unhide rows on multiple sheets based on a validation list on a single sheet
    By bbarth in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-30-2015, 02:10 PM
  2. Hide/Unhide rows based on Yes or No answers to questions by dropdown list.
    By Johny1 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-12-2015, 06:57 PM
  3. Macro to unhide/hide worksheet according to value in dropdown list
    By caza2 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-21-2014, 07:33 PM
  4. hide/unhide columns based on dropdown list.
    By bg781np in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-18-2013, 07:13 PM
  5. Automatically Hide/Unhide Columns Based Dropdown List
    By Kapil.gour in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-27-2012, 07:39 AM
  6. Hide/Unhide rows on multiple sheets when dropdown selected from sheet 1
    By tammhelp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-02-2012, 10:33 PM
  7. Unhide/Hide column when choosing dropdown list
    By yuzi in forum Excel General
    Replies: 4
    Last Post: 10-06-2009, 12:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1