Simple worksheet selection question???

[Simon Lloyd]

Hi all, How do i select a worksheet based on a cells contents?, if i
have cell A1 containg a dropdown list of all the worksheets Like Week1,
Week2 etc how do i create the code to call a work sheet based on that
name instead of Worksheets("Week1").Select, so something like
Sub weekselect()
Dim t As Worksheet
t.Name = Range("f10").Value
Worksheets(t).Visible=True
Worksheets(t).Select
Worksheets("Week Selection").Visible = False
End With
End Sub

Regards,
Simon

 

[Gordon Smith \(eMVP\)]

Hi all, How do i select a worksheet based on a cells contents?, if i
have cell A1 containg a dropdown list of all the worksheets Like
Week1, Week2 etc how do i create the code to call a work sheet based
on that name instead of Worksheets("Week1").Select, so something like
Sub weekselect()
Dim t As Worksheet
t.Name = Range("f10").Value
Worksheets(t).Visible=True
Worksheets(t).Select
Worksheets("Week Selection").Visible = False
End With
End Sub

Regards,
Simon

Try this:

 

[Gordon Smith \(eMVP\)]

Hi all, How do i select a worksheet based on a cells contents?, if i
have cell A1 containg a dropdown list of all the worksheets Like
Week1, Week2 etc how do i create the code to call a work sheet based
on that name instead of Worksheets("Week1").Select, so something like
Sub weekselect()
Dim t As Worksheet
t.Name = Range("f10").Value
Worksheets(t).Visible=True
Worksheets(t).Select
Worksheets("Week Selection").Visible = False
End With
End Sub

Regards,
Simon

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Validation.Type = 3 Then
Sheets(Target.Value).Select
End If
End Sub

 

[Simon Lloyd]

Gordon thanks for the reply, i didn't quite understand what the code wa
meant to do as it doesn't look to a cell for its information. M
situation is this, i have a front sheet with an autoshape button on i
next to that button is a cell with list validation in it, that lis
refers to week1, week2....etc these are the names of the worksheets,
want to be able to select one of these weeks in the dropdown then clic
my autoshape which will run the code and select the appropriat
worksheet and hide the front sheet.

Regards,
Simo

 

[Simon Lloyd]

Sorted this one!.....seems like i was trying to use a sledgehammer to
crack a nut!!!

Simple when you take a break and come back to it!

Regards,
Simon

Sub weekselect()
t = Range("F10").Value
Worksheets(t).Visible = True
Worksheets(t).Select
Worksheets("Week Selection").Visible = False
End Sub

 

[Guest]

Hi, Simon,
You could also try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SheetName As Range
Set SheetName = Me.Range("SheetName")
Set Intersection = Intersect(Target, SheetName)
If Not Intersection Is Nothing Then
If Target.Validation.Type = 3 Then
Sheets(Target.Value).Select
End If
End If
End Sub

Make sure it is pasted into the codepage for the worksheet that contains
your list, not a normal code module.

It relies on the cell containing your dropdown list being called "SheetName"
If you do it like this, tou won't need to select from the list then click a
button, the code will do it all for you in one go.

It also checks that the change is being made to the "Sheetname" cell - if a
change to any other cell is made, the code won't run - otherwise, it would
try to activate a worksheet with a name of whatever you typed into ANY cell.

Cheers

Pete