| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkSheet Change or select based on a cell value
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkSheet Change or select based on a cell value
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkSheet Change or select based on a cell value
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkSheet Change or select based on a cell value
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkSheet Change or select based on a cell value
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkSheet Change or select based on a cell value
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkSheet Change or select based on a cell value
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 -- Cesar "Dave Peterson" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
WorkSheet Change or select based on a cell value
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.) Cesar wrote: 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 -- Cesar "Dave Peterson" wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I set up worksheet tabs to change based on a workbook cell | Excel Worksheet Functions | |||
| Select correct worksheet based on data entered into a cell | Excel Worksheet Functions | |||
| Select correct worksheet based on data entered into a cell | Excel Programming | |||
| change current cell colour based on the value of adjacent cell on other worksheet | Excel Programming | |||
| Can I select a worksheet based upon a cell criteria?(for printing) | Excel Worksheet Functions | |||
Linear Mode
