Unhiding worksheets

[Keef]

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]

Hi. Try

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

 

[brans1982]

It doesn't work as you are tyring to unhide a range.

Try...

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

 

[GTO]

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.

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]

Wow, I can't belive how quickly you all repiled, Thank you so much

They all work, i think your all ace