Unhide Worksheets Automatically

Angryjames · Dec 22nd 2010

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

Code

Private Sub worksheet_change(ByVal target As Excel.Range)
Dim ws As Worksheet
 
For Each ws In Worksheets
If ws.Name <> "Sheet1" And ws.Name <> Worksheets("Sheet1").Range("A1").Value Then
ws.Visible = False
End If
If ws.Name = Worksheets("Sheet1").Range("A1").Value Then
ws.Visible = True
End If
Next ws
 
End Sub

Display More

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.

Barb-B · Dec 22nd 2010

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.

Angryjames · Dec 22nd 2010

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

Code

Private Sub worksheet_change(ByVal target As Excel.Range)
 Dim ws As Worksheet
 
 For Each ws In Worksheets
  If ws.Name <> "Sheet1" And ws.Name <> Worksheets("Sheet1").Range("A1:A10").Value Then
  ws.Visible = False
 End If
  If ws.Name = Worksheets("Sheet1").Range("A1:A10").Value Then
  ws.Visible = True
 End If
  Next ws
 
End Sub

Display More

Barb-B · Dec 22nd 2010

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:

Code

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim ws As Worksheet
    Dim inlist As Boolean
 
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        On Error Resume Next
        For Each ws In Worksheets
            inlist = False
            inlist = Range("A1:A10").Find(ws.Name).Row
            ws.Visible = (inlist Or ws.Name = "Sheet1")
        Next ws
    End If
 
End Sub

Display More

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.

Angryjames · Dec 22nd 2010

Re: Unhide Worksheets Automatically

Thank you that works exactly Like I want it to, I appreciate your help

Angryjames · Dec 22nd 2010

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.

AAE · Dec 22nd 2010

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.

shiven.k · Feb 7th 2013

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

cytop · Feb 7th 2013

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.

Unhide Worksheets Automatically

Participate now!

Share