PDA

View Full Version : [SOLVED] Using DropDown list to navigate through Sheets - Help with Code size reduction



vijyat
10-15-2014, 07:22 PM
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
10-15-2014, 11:50 PM
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
10-15-2014, 11:51 PM
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
10-15-2014, 11:52 PM
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
10-15-2014, 11:56 PM
i refreshed the page, saw no replies...

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

snb
10-16-2014, 03:20 AM
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
10-16-2014, 07:20 AM
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 :rotlaugh:. 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
10-16-2014, 08:07 AM
you are welcome.
thanks for the feedback and marking the thread as solved.

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

SamT
10-16-2014, 08:14 AM
:bigdance2I'm late for the party .:crying:

But, I brought a gift. :bggift: