Unhide Worksheets Automatically

  • Hoping someone can help me

    Im trying to create a workbook that from the first page (worksheet) a user can select predertimend options from a dropdown (This i understand how to do), and based on the selections, This will be in a range of cells IE. A1 to A10

    I will have worksheets hidden but named the same as these selection, I just want to have the corresponding worksheet unhide when selected. I have a code to do it with one cell only but i need it in a range of cells. this is what i have



    I thought i would try replacing ("A1") with ("A1:A10") but that didnt work I received an error message, Mind you im a little new to all this stuff.

    Any help would be appreciated.

  • Re: Unhide Worksheets Automatically


    Is it your goal to have all sheets hidden, except for Sheet1 and those showing in range("A1:A10")? I ask because that is the result you would get from the code above that you've gotten to work for the single cell range.

  • Re: Unhide Worksheets Automatically


    Yes that is my goal,


    I have type it in using A1:A10 and i get an error when that the only thing i change


    The Error I get When I write the Code as such is


    Runtime Error '13"
    Type Mismatch.


    Maybe I am doing something else wrong


  • Re: Unhide Worksheets Automatically


    You're getting the error because the .Value property can be used only for single cell ranges. A1:A10 has multiple values, so they can't all be assigned to a single variable (unless it's an array - but that would be a lesson for another day). What you want to test is whether the sheet name is found in A1:A10. The code below will do that:



    The If statement using Intersect function prevents the whole set of code from firing every time the user changes something in another area of the sheet.

  • Re: Unhide Worksheets Automatically


    Thank you that worked great, Now I just need one more thing if you don't mind.


    I have been taking a stab at it for a while now, but i want the last save date , to be put in lets say Cell C3. Ive tried a few different methods and I've gotten myself confused.

  • Re: Unhide Worksheets Automatically


    Hello Angryjames,


    Threads are limited to a single topic with closely related follow-on questions permitted.
    Your question in your last post appears to be unrelated to the original topic of this thread.


    Please start a new thread for this question. Thanks.

  • Re: Unhide Worksheets Automatically


    Hi Barb-B,


    It was good one, in the same context, I just need one help...condition are as follows:


    Sheets:


    Data
    M6A_Factory
    M6A_Region
    M5_Factory
    M5_Region


    Now, if in the Data Sheet Cell A1 has the value = "M5" and Cell A2="Factory" then unhide the sheet named "M5_Factory"


    Please help..thanks in advance,


    Regards,
    Shiven

  • Re: Unhide Worksheets Automatically


    Please don't post questions in threads started by other members.


    If you have a query, start your own thread, give it a clear and concise title and explain your issue fully. If you think this thread will help clarify your issue (which it probably will), you can include a link to it by copy the URL of this page and pasting into your message.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!