Select from drop down & Go To Worksheet

[hsandeep]

My Excel workbook contains lot of worksheets à Week1 to Week52.
I want to move my cursor in various worksheets i.e. open various worksheets 1 at a time. To achieve this, I need à
1. A ‘link’ or ‘something of that sort’, mouse clicking on which enables the action of, example F GoToWeek21 worksheet is accomplished i.e. worksheet [Week21] tab named is opened now.
2. Further extension of it à A drop down list box required (say, in cell E4) which should contain ALL the worksheet’s tab names in it, out of which I would ‘select’ a worksheet’s tab name & click ‘a button’ with a name on it like ‘Enter’ or ‘OK’ or ‘Go There’ & the action of the selected worksheet getting opened up is accomplished.
3. My workbook is opened & currently I am in [Week1] & I am using Microsoft Office 2007 suite.
Please help.
Sandeep

 

[MickG]

Hi, I'm not sure if it is the same with 2007, but with ealier versions, if you right click on the scroll arrows at the left hand end of the sheet tabs, you will get a drop down box with a list of the sheet names ,that you can select from.
Mick

 

[davidam]

Do you know how to do a select case?

 

[davidam]

This what I would do:

Create a list of all of the worksheet names in a column
beside that column drag a list of numbers 1,2 3 etc
Next click on Data > Validation and Allow: List; point it to the first column of your two columns. Then pick a cell nearby; click Insert > function> Vlookup. The lookup value is the cell with the validation table, the table array is the range of the two columns and the col index number is 2 the range lookup is false.
Now make a button, not a command button, but a button from the forms toolbar. As soon as you release the left mouse after dragging in the button a macro menu will pop up, click on new. The first line of the macro will be:

Select Case Range("the cell with the vlookup").value
Case 1 
Sheets("the sheet that corresponds to the number 1").Select
'etc with a number for each of our sheets.
End Select

this goes between the Sub button and end sub lines that you will already find there

 

[davidam]

P.S. to operate you will click on the cell with the list, select the one you want and then click the button and it will take you to the sheet you want

 

[Smitty]

Assuming you have a validation list in each sheet and the sheet names in the list exactly match the sheet names you can use a global change event:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("A1")
             '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
            '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met (do your thing here...)
            With Sheets(Target.Value)
                .Visible = True
                .Activate
            End With
End Sub

Any sheet selection in A1 in any sheet will automatically activate the sheet selected.

Note the code goes in the ThisWorkbook module.

HTH,

 

[davidam]

So you put a validation list on each sheet and you only have to select a name and it goes to that sheet?

 

[Smitty]

So you put a validation list on each sheet and you only have to select a name and it goes to that sheet?

Correct. Provided the selections in the list are an exact match to the sheet names themselves.

 

[hsandeep]

Smitty,
This has worked quite well.
Is it possible to ‘punch’ the worksheet’s name in the cell address F Z10 of worksheet [Week5] of my workbook, where usually I am often & ‘jump’ to that typed tab name worksheet. If yes, what changes should I do in the codes?
Your process will involve me remembering EXACTLY worksheet’s tab name where I need to jump. Moreover, the worksheet’s tab name is also modified/added/deleted frequently. So, the earlier help sought by me à such that ALL worksheets name appear AUTOMATICALLY in the drop down list box & I could select from that drop down list box the tab name where I need to jump would help me in a much better way. Cell address for this drop down list box will be kept frozen at Z10 of worksheet with tab name [Week5]. Sir, please accomplish this.
Sandeep

 

[Smitty]

Here is the code for Zack Barresse's Table of COntents generator, which will build a hyperlinked list of all of your sheets.

Sub CreateTOC()
    '   Code by Zack Baresse
    If ActiveWorkbook Is Nothing Then
        MsgBox "You must have a workbook open first!", vbInformation, "No Open Book"
        Exit Sub
    End If
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    
        Dim ws As Worksheet, _
            ct As Chart, _
            shtName As String, _
            nrow As Long, _
            tmpCount As Long, _
            i As Long, _
            numCharts As Long
        
        nrow = 3
        i = 1
        numCharts = ActiveWorkbook.Charts.Count
        
        On Error GoTo hasSheet
        Sheets("Table of Contents").Activate
        If MsgBox("You already have a Table of Contents page.  Would you like to overwrite it?", _
        vbYesNo + vbQuestion, "Replace TOC page?") = vbYes Then GoTo createNew
        Exit Sub
hasSheet:
    Sheets.Add Before:=Sheets(1)
    GoTo hasNew
createNew:
    Sheets("Table of Contents").Delete
    GoTo hasSheet
hasNew:
    tmpCount = ActiveWorkbook.Charts.Count
    If tmpCount > 0 Then tmpCount = 1
        ActiveSheet.Name = "Table of Contents"
        
        With Sheets("Table of Contents")
            '.Cells.Interior.ColorIndex = 4
                With .Range("B2")
                    .Value = "Table of Contents"
                    .Font.Bold = True
                    .Font.Name = "Calibri"
                    .Font.Size = "24"
                End With
        End With
        
        For Each ws In ActiveWorkbook.Worksheets
            nrow = nrow + 1
            With ws
                shtName = ws.Name
                With Sheets("Table of Contents")
                    .Range("B" & nrow).Value = nrow - 3
                    .Range("C" & nrow).Hyperlinks.Add _
                        Anchor:=Sheets("Table of Contents").Range("C" & nrow), Address:="#'" & _
                        shtName & "'!A1", TextToDisplay:=shtName
                    .Range("C" & nrow).HorizontalAlignment = xlLeft
                End With
            End With
        Next ws
        
        If numCharts <> 0 Then
            For Each ct In ActiveWorkbook.Charts
                nrow = nrow + 1
                shtName = ct.Name
                With Sheets("Table of Contents")
                    .Range("B" & nrow).Value = nrow - 3
                    .Range("C" & nrow).Value = shtName
                    .Range("C" & nrow).HorizontalAlignment = xlLeft
                End With
            Next ct
        End If
        
        With Sheets("Table of Contents")
            With .Range("B2:G2")
                .MergeCells = True
                .HorizontalAlignment = xlLeft
            End With
        
            With .Range("C:C")
                .EntireColumn.AutoFit
                .Activate
            End With
            .Range("B4").Select
        End With
    
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    MsgBox "Done!" & vbNewLine & vbNewLine & "Please note: " & _
        "Charts are listed after regular " & vbCrLf & _
        "worksheets and will not have hyperlinks.", vbInformation, "Complete!"
End Sub