Using DropDown list to navigate through Sheets - Help with Code size reduction

[vijyat]

Hi,

I have an excel file with 29 sheets. All sheets are numbered in accordance with the code that I wrote. The dropdown menu is linked to Cell "O2" in all the sheets, just as a placeholder. The linked cell can be changed. When the first value in the list is chosen from dropdown the O2 Cell displays a value of 1 and so on and so forth. I am looking for some help to reduce the code as I believe there are better ways to write it and I can learn at the same time. The code works perfectly at the moment.

Appreciate any help.
Thanks

Function GoSheet(strWS)
    Sheets(strWS).Select
    Range("A1").Activate
End Function


Sub LaunchSheet()


    If Range("O2") = 1 Then
    GoSheet (Sheet1.Name)
    ElseIf Range("O2") = 2 Then
    GoSheet (Sheet2.Name)
    ElseIf Range("O2") = 3 Then
    GoSheet (Sheet3.Name)
    ElseIf Range("O2") = 4 Then
    GoSheet (Sheet4.Name)
    ElseIf Range("O2") = 5 Then
    GoSheet (Sheet5.Name)
    ElseIf Range("O2") = 6 Then
    GoSheet (Sheet6.Name)
    ElseIf Range("O2") = 7 Then
    GoSheet (Sheet7.Name)
    ElseIf Range("O2") = 8 Then
    GoSheet (Sheet7.Name)
    ElseIf Range("O2") = 9 Then
    GoSheet (Sheet9.Name)
    ElseIf Range("O2") = 10 Then
    GoSheet (Sheet10.Name)
    ElseIf Range("O2") = 11 Then
    GoSheet (Sheet11.Name)
    ElseIf Range("O2") = 12 Then
    GoSheet (Sheet12.Name)
    ElseIf Range("O2") = 13 Then
    GoSheet (Sheet13.Name)
    ElseIf Range("O2") = 14 Then
    GoSheet (Sheet14.Name)
    ElseIf Range("O2") = 15 Then
    GoSheet (Sheet15.Name)
    ElseIf Range("O2") = 16 Then
    GoSheet (Sheet16.Name)
    ElseIf Range("O2") = 17 Then
    GoSheet (Sheet17.Name)
    ElseIf Range("O2") = 18 Then
    GoSheet (Sheet18.Name)
    ElseIf Range("O2") = 19 Then
    GoSheet (Sheet19.Name)
    ElseIf Range("O2") = 20 Then
    GoSheet (Sheet20.Name)
    ElseIf Range("O2") = 21 Then
    GoSheet (Sheet21.Name)
    ElseIf Range("O2") = 22 Then
    GoSheet (Sheet22.Name)
    ElseIf Range("O2") = 23 Then
    GoSheet (Sheet23.Name)
    ElseIf Range("O2") = 24 Then
    GoSheet (Sheet24.Name)
    ElseIf Range("O2") = 25 Then
    GoSheet (Sheet25.Name)
    ElseIf Range("O2") = 26 Then
    GoSheet (Sheet26.Name)
    ElseIf Range("O2") = 27 Then
    GoSheet (Sheet27.Name)
    ElseIf Range("O2") = 28 Then
    GoSheet (Sheet28.Name)
    ElseIf Range("O2") = 29 Then
    GoSheet (Sheet29.Name)
    
    End If
  End Sub

[GTO]

Greetings vijyat,

This would be another way.

In Sheet1's Module:

Option Explicit
    
Private Sub Worksheet_Change(ByVal Target As Range)
    
    '// This is in case you want to jump to A1 of the selected sheet, //
    '// upon making a selection in the Data Validation drop-down.     //
    
    '// Ensure we are only running when just one cell changes, and that this      //
    '// cell is the one with the data validation.  (I chose C1 to put the DV in.  //
    If Target.Count = 1 Then
      If Target.Address = Me.Cells(3).Address Then
        '// A safety to ensure the sheet exists.                                  //
        If SheetExists(Me.Cells(3).Value) Then
          '// Jump to the sheet/cell desired                                      //
          Application.Goto ThisWorkbook.Worksheets(Me.Cells(3).Value).Range("A1"), True
        End If
      End If
    End If
    
End Sub
  
Private Function SheetExists(SheetName As String) As Boolean
  '// Override/ignore the error raised if the sheet does not exist, //
  '// in which case SheetExists will remain FALSE.                  //
  On Error Resume Next
  SheetExists = ThisWorkbook.Worksheets(SheetName).Name = SheetName
  On Error GoTo 0
End Function

I chose cell C1 on Sheet1 for the DV. We could populate the DV's List when the workbook opens.

In ThisWorkbook Module:

Option Explicit
    
Private Sub Workbook_Open()
Dim strSheetNames As String
Dim wks           As Worksheet
      
  '// For each visible sheet in the workbook, concatenate the sheet names //
  '// together, for our DV List argument.                                 //
  For Each wks In ThisWorkbook.Worksheets
    If wks.Visible = xlSheetVisible Then
      strSheetNames = strSheetNames & wks.Name & ","
    End If
  Next
  
  '// Then strip the last comma off the string.                         //
  strSheetNames = Left$(strSheetNames, Len(strSheetNames) - 1)
  
  '// Create the DV in C1 of Sheet1                                     //
  With Sheet1.Range("C1").Validation
    .Delete
    .Add Type:=xlValidateList, _
         AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, _
         Formula1:=strSheetNames
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
  End With
    
End Sub

Hope that helps,

Mark

[Bob Phillips]

Sub LaunchSheet() 
     
     
    If Range("O2").Value >= 1 And Range("O2").Value <=29 Then

        Application.Goto Worksheets"(Sheet" & Range("O2").Value).Range("A1")
    End If
End Sub

[mancubus]

provided that you have a correct listing of worksheet names in O2, you only need:

Sub LaunchSheet()
    Application.Goto Worksheets(Range("O2")).Range("A1")
End Sub

[mancubus]

i refreshed the page, saw no replies...

i posted my solution and kinda magic happens:
there are 3 replies to thread now.

[snb]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "$O$2" Then Application.Goto [indirect(O2&"!A1")]
End Sub

[vijyat]

Hi Mancubus/ Mark/xld/snb

Thank you for all your inputs. Each and every code that you provided works. I went ahead with mancubus's code as it was the shortest . Although I appreciate all the efforts. It taught me how I can use different approaches for the same task. @ Mancubus: It's weird even though my sheets were linked as-is in the object explorer and it didn't matter which order they were in the tabs within excel. With your code I had to drag the sheet tabs to match the order in the drop down. Thanks again though.

[mancubus]

you are welcome.
thanks for the feedback and marking the thread as solved.

actually all for replies include the application's GoTo method.

[SamT]

I'm late for the party .

But, I brought a gift.