Unhiding worksheets

Keef

New Member
Joined
Mar 4, 2011
Messages
2
Hi everyone,

This is the first time I've tried any VBA, but this is what I've been trying

I've been asked to create a workbook with a list on it (I've used validation to create a drop down list); which contains the names of the other worksheets in the workbook. THese other sheets are hidden. when the user selects an item from the list the worksheet unhides.

I thought I'd create a variable, pop the value from the cell with sheet name in it in the variable and then change the worksheet with that name to visible

But i can't get it work, i've looked at loads of examples but they also seem really complicated

here's what I've done (don't laugh), it doesn't work

Sub unhide_ws()
Dim showsheet As String
showsheet = Worksheets("Home").Range("B3")
Worksheet(showsheet).Visible = True
End Sub

I'd like to know whats wrong with it, and if my initial ideas OK

Any help would be amazing

Thanks Very Much
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Hi. Try

Rich (BB code):
Sub unhide_ws()
Dim showsheet As String
showsheet = Worksheets("Home").Range("B3")
Worksheets(showsheet).Visible = True
End Sub
 

brans1982

Active Member
Joined
Mar 25, 2009
Messages
263
It doesn't work as you are tyring to unhide a range.

Try...

Worksheets("Home").Visible = True
Range("B3").Select
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Hi there,

Just an example; this would hide the "other" sheets, excepting the most recent pick. This code would go in the sheet's module that you have the DV on.
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet
    
    If Not Application.Intersect(Target, Range("B2")) Is Nothing Then
        If Target.Count = 1 Then
            For Each wks In ThisWorkbook.Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
                wks.Visible = xlSheetVeryHidden
            Next
        
            ThisWorkbook.Worksheets(Range("B2").Text).Visible = xlSheetVisible
        End If
    End If
End Sub
 

Keef

New Member
Joined
Mar 4, 2011
Messages
2
Wow, I can't belive how quickly you all repiled, Thank you so much

They all work, i think your all ace :)
 

Forum statistics

Threads
1,086,235
Messages
5,388,623
Members
402,127
Latest member
Jemx

Some videos you may like

This Week's Hot Topics

Top