go to worksheet depending on Cell value

[CuriousGeorg]

Hi guys, just a quick question and one im sure is quite simple.

It's a method of moving between worksheets.

what I have is a dropdown list of the worksheets. The cell link is say A1.

What I want the VBA to do is.. upon pressing the "GO" button is for the VBA to say

"IF A1 = "1" then goto sheet "January"
IF A1 = "2" then goto sheet "February"

nothing too fancy but theres a lot of info on the dashboard and this makes it visually more appealling and more user friendly than having say hyperlinks all over.

[CuriousGeorg]

Fixed it eventually, apologies for this post.. can be nuked if necessary

else



If Range("A1") = "1" Then
Worksheets("January").Activate
End If

[GTO]

No reason to apologize, we are all learning and/or reminding ourselves of what most recently fell out of our brain...

With an old-fashioned commandbutton on Sheet1 (both name on tab and default CodeName), in the worksheet's module:



Option Explicit

Public Sub PickASheet()
Dim wks As Worksheet

On Error Resume Next
Set wks = ThisWorkbook.Worksheets(Me.Cells(2, 2).Value)
On Error GoTo 0

If Not wks Is Nothing Then
Application.Goto ThisWorkbook.Worksheets(Me.Cells(2, 2).Value).Range("A1"), -1
Else
MsgBox "Worksheet """ & Me.Cells(2, 2).Value & """ does not exist...", vbInformation Or vbOKOnly, "Oopsie"
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count = 1 And Target.Address(0, 0, xlA1) = "B2" Then
PickASheet
End If

End Sub


Cell B2 would have Data Validation (DV) of a list of months.

In the command button's click event (in a Standard Module):



Option Explicit

Sub Button2_Click()
Call Sheet1.PickASheet
End Sub


Just in case it helps (and I had already typed it out...)

Happy coding!

Mark

[holycow]

Here is another option, assuming data validation list of month numbers is in A1, code goes in the Sheet Module.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target <> "" Then
On Error GoTo errhandler
mth = Format(DateSerial(2000, Range("A1"), 1), "mmmm")
Sheets(mth).Activate
End If
Exit Sub
errhandler:
MsgBox "There is no sheet for " & mth
End Sub

[mancubus]

Worksheets(Application.GetCustomListContents(4)(Range("A1"))).Activate

GetCustomListContents(4) holds the month names January to December (with indexes 1-12)

[holycow]

Nice, thanks mancubus

[mancubus]

you are welcome.
thanks for the feedback.