WorkSheet Change or select based on a cell value

[Cesar]

Hi,

How can I change/select a worksheet based on a value of a cell?

I named a cell "GRADE" (let's say on my Sheet1) which is going to change
based on a user entry. This GRADE value is an alphanumeric value, if GRADE =
61026S select Sheet2, if GRADE = 61042 select Sheet2, and so on..

Thanks

 

[Per Jessen]

Hi Cesar

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("GRADE").Address Then
Select Case Target.Value
Case Is = "61026S"
Sheets("Sheet2").Activate
Case Is = "61042"
Sheets("Sheet2").Activate
End Select
End If
End Sub

Regards,
Per

 

[Cesar]

Do I have to write this code in the Sheet1 or in the ThisWorkbook Object?

Thanks

 

[Dave Peterson]

This will be placed under the sheet that needs the behavior (sheet1).

 

[Cesar]

Thanks Dave,

I did it in that way, but it works just the first time, when the value of
GRADE change (on Sheet1) it did not select the appropieated Sheet, let say
originally it GRADE was 61042 and then it selected Sheet 61042, but when
GRADE changes again to, let say, 61026S the selected sheet stays on 61042 and
does not go to the sheet 61026S.
The values for GRADE is pulled/refresh from a MS Query from a SQL data base,
does this has something to do with it?

Thanks again,

 

[Dave Peterson]

When you typed the S in 61026S, did you use upper or lower case? The comparison
is case sensitive (as written).

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("GRADE").Address Then
Select Case lcase(Target.Value)
Case Is = lcase("61026S")
Sheets("Sheet2").Activate
Case Is = lcase("61042")
Sheets("Sheet2").Activate
End Select
End If
End Sub

If this doesn't help, post your current code.

And make sure that you used the correct names in the code--make sure that they
match the names on the worksheet tab.

Thanks Dave,

I did it in that way, but it works just the first time, when the value of
GRADE change (on Sheet1) it did not select the appropieated Sheet, let say
originally it GRADE was 61042 and then it selected Sheet 61042, but when
GRADE changes again to, let say, 61026S the selected sheet stays on 61042 and
does not go to the sheet 61026S.
The values for GRADE is pulled/refresh from a MS Query from a SQL data base,
does this has something to do with it?

Thanks again,

 

[Cesar]

Thanks Dave, here is the code, there are some GRADE values that don't have
letters, and still dont work

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("GRADE").Address Then
Select Case Target.Value
Case Is = "69026S"
Sheets("69026S").Activate
Case Is = "61042"
Sheets("61042").Activate
Case Is = "61035"
Sheets("61035").Activate
Case Is = "61033"
Sheets("61033").Activate
Case Is = "61026H"
Sheets("61026H").Activate
End Select
End If
End Sub

Thanks again

 

[Dave Peterson]

I'm still betting that you're typing lower case letters in that cell and that
breaks the code (which looks for upper case).

But since what you type into the cell is the name of the sheet, you can use
different code that does a little less work.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then
Exit Sub
End If

If Intersect(Target, Me.Range("GRADE")) Is Nothing Then
Exit Sub
End If

On Error Resume Next
Worksheets(CStr(Target.Value)).Select
If Err.Number <> 0 Then
Err.Clear
MsgBox "No visible sheet by that name!"
End If
On Error GoTo 0

End Sub

If it doesn't work, you're going to have to describe what happens when you try
it. And give some details when the code does work.

(My bet is that you're not typing the exact name of the worksheet.)

Thanks Dave, here is the code, there are some GRADE values that don't have
letters, and still dont work

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("GRADE").Address Then
Select Case Target.Value
Case Is = "69026S"
Sheets("69026S").Activate
Case Is = "61042"
Sheets("61042").Activate
Case Is = "61035"
Sheets("61035").Activate
Case Is = "61033"
Sheets("61033").Activate
Case Is = "61026H"
Sheets("61026H").Activate
End Select
End If
End Sub

Thanks again