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
--
Cesar

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" skrev i meddelelsen
...
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
--
Cesar

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

Thanks
--
Cesar


"Per Jessen" wrote:

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" skrev i meddelelsen
...
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
--
Cesar

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

Cesar wrote:

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

Thanks
--
Cesar

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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
--
Cesar



--

Dave Peterson

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


"Dave Peterson" wrote:

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

Cesar wrote:

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

Thanks
--
Cesar

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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
--
Cesar



--

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.


Cesar wrote:

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

"Dave Peterson" wrote:

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

Cesar wrote:

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

Thanks
--
Cesar

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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
--
Cesar



--

Dave Peterson


--

Dave Peterson