Solved: Selecting sheet based on cell entry

[Sir Babydum GBE]

Hello,

How do I say in code that whatever a person types in A1 of sheet one, check to see if there is another sheet with that name, and if so, go to it.

So if I type "Sheet2" in A1 of sheet one, on hitting the return key, sheet 2 will be selected.

Thanks

BD

[Bartek]

Hi


So if I type "Sheet2" in A1 of sheet one, on hitting the return key, sheet 2 will be selected.

This is somewhat tricky, as you cannot directly reference the sheet with a string varaible holding its name. But you may do it anyway by looping through worksheets. For example, in code for Sheet1 put:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Xi As Integer
If Target = Range("A1") Then
For Xi = 1 To Worksheets.Count
If Sheets(Xi).Name = Cells(1, 1) Then Sheets(Xi).Activate
Next Xi
End If
End Sub

If you enter the name of worksheet that does not exist there will be neither any action nor error message.

[Sir Babydum GBE]

Hi



This is somewhat tricky, as you cannot directly reference the sheet with a string varaible holding its name. But you may do it anyway by looping through worksheets. For example, in code for Sheet1 put:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Xi As Integer
If Target = Range("A1") Then
For Xi = 1 To Worksheets.Count
If Sheets(Xi).Name = Cells(1, 1) Then Sheets(Xi).Activate
Next Xi
End If
End Sub

If you enter the name of worksheet that does not exist there will be neither any action nor error message.Hmm, thanks very much for this, unfortunately it's not working on my workbook.

[mdmackillop]

Hi Sir BD,
Add "Option Compare Text" before the code, which I'm sure you will have pasted in the correct Worksheet module.
Regards
MD

[OBP]

Bartek, what do mean you can't select a worksheet using a string?
This works perfectly

Dim sheetname As String
Range("a1").Select
sheetname = ActiveCell
Sheets(sheetname).Select

as does this

Dim sheetname As String
sheetname = Range("a1")
Sheets(sheetname).Select

[Bartek]

Bartek, what do mean you can't select a worksheet using a string?

Sorry for my mistake :oops: Of course string variable works.
I got an error when first trying to use this method and then assumed that it does not work, but it was probably due to a mistake in my code (wrong assignment or whatever).

BabyDum: you could replace the code in Worksheet_Change with Sheets(CStr(Range("A1"))).Select

[Sir Babydum GBE]

Thanks everyone - working fine now.

:)

[johnske]

Alt...
Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target = Me.[A1] Then Exit Sub
On Error Resume Next '< Error=no sheet by that name
Sheets(Target.Text).Activate
End Sub