Open hidden sheets from a drop down list selection

[Guest]

I have a file with many hidden sheets eg Sheet1, Sheet2...Sheet50. What I
want is some code which looks at the selection from a drop down list (on a
visible sheet) and the open that sheet. ie if I select Sheet5 from the drop
down list, Sheet5 becomes visible. I then want to hide it again once I have
finished with it (I can do that bit!). The reason I am doing this is because
otherwise I will have too many sheets to tab through.

Any thoughts?

 

[Gord Dibben]

Ant

Manually you can leave the sheets unhidden but right-click on the sheet
navigation buttons at bottom left to get a 15 name list of sheets with "more
sheets" to pick from.

You would have to use a macro to unhide the sheets.

I would prefer, if using a macro anyway, to leave the sheets unhidden and use
a macro to give me a list of sheets to pick from.

Use Bob Phillips BrowseSheets macro(posted below)which displays a form with
option buttons to select a sheet. I personally like this one.

Sub BrowseSheets()
Const nPerColumn As Long = 38 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
'dialog caption

Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim cLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton

Application.ScreenUpdating = False

If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add

With thisDlg

.Name = sID
.Visible = xlSheetHidden

'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40

For i = 1 To ActiveWorkbook.Worksheets.Count

If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters > cMaxLetters Then
cMaxLetters = cLetters
End If

iBooks = iBooks + 1
.OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5
.OptionButtons(iBooks).text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13

Next i

.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24

CurrentSheet.Activate

With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With

.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
.Delete
End With
End Sub


Gord Dibben Excel MVP

 

[Chris Lavender]

Hi Ant


Assuming F7 displays the name of the sheet selected - ie, is either the
linked cell for your (ActiveX) dropdown list or an offset formula referring
to the linked cell for an MSForms dropdown control...

Sub opensheet()
Dim mysheet
mysheet = Sheets(1).Range("F7").Value
Sheets(mysheet).Visible = True
Sheets(mysheet).Select
End Sub

and then you'd probably want a command button on each of the hidden sheets
to return to Sheet1 and hide the sheet you were in...

Sub closesheet()
Set mysheet = ActiveSheet
Sheets(1).Select
mysheet.Visible = False
Set mysheet = Nothing
End Sub

Best rgds
Chris Lav

 

[Guest]

Thanks Chris/Gord,

I looked at your code Chris and made a few changes - works perfectly. I had
a look at Gord's code which was interesting - something I can use in another
file. I like the use if a dialog box, something I haven't used before. Thanks
again guys.

Sub OpenSheets()

Dim OpenSheet As Worksheet
Set OpenSheet = Nothing
On Error Resume Next
Set OpenSheet = Sheets(ActiveSheet.Range("E5").Value)
If OpenSheet Is Nothing Then
Exit Sub
Else
With OpenSheet
..Visible = xlSheetVisible
..Select
Range("I4").Select
End With
End If

End Sub