Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
I have an Excel workbook with 49 tabs. Each tab starting with sheet 2 has a
list of tasks to be completed. Once each line item is signed and the analyst's time is entered, the line item turns blue (meaning complete). The job cannot be closed until all line items are blued out. The next to the last line item that is completed, is a trigger for the Processor to go in and close the job out and blue the last item. I would like to write something so that when the trigger line item has time entered, the tab turns red, then when the Processor closes the job, the sheet tab turns blue. **Note: The Trigger and Processor line items are not located in the same place on every worksheet. I defined the two time cells as TriggerComplete and ProcessComplete and have placed this code under "This Workbook" : Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range If Not (Intersect(Target, Range(Cells(2, 8), Cells(30, 8))) Is Nothing) Then If ((Sh.Range("TriggerComplete").Value 0) And (Sh.Range("ProcessComplete").Value = 0)) Then Sh.Tab.ColorIndex = 3 ElseIf ((Sh.Range("TriggerComplete").Value 0) And (Sh.Range("ProcessorComplete").Value 0)) Then Sh.Tab.ColorIndex = 5 Else Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub This works great, but only if I am changing time on the second worksheet. I tried to define the cell names on the other worksheets but it either doesn't save or changes the reference to a different sheet, thus making the code only work if I change time on that particular sheet. Can anyone help? I would greatly appreciate it. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
I am using Excel 2003.
"KC_Cheer_Coach" wrote: I have an Excel workbook with 49 tabs. Each tab starting with sheet 2 has a list of tasks to be completed. Once each line item is signed and the analyst's time is entered, the line item turns blue (meaning complete). The job cannot be closed until all line items are blued out. The next to the last line item that is completed, is a trigger for the Processor to go in and close the job out and blue the last item. I would like to write something so that when the trigger line item has time entered, the tab turns red, then when the Processor closes the job, the sheet tab turns blue. **Note: The Trigger and Processor line items are not located in the same place on every worksheet. I defined the two time cells as TriggerComplete and ProcessComplete and have placed this code under "This Workbook" : Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range If Not (Intersect(Target, Range(Cells(2, 8), Cells(30, 8))) Is Nothing) Then If ((Sh.Range("TriggerComplete").Value 0) And (Sh.Range("ProcessComplete").Value = 0)) Then Sh.Tab.ColorIndex = 3 ElseIf ((Sh.Range("TriggerComplete").Value 0) And (Sh.Range("ProcessorComplete").Value 0)) Then Sh.Tab.ColorIndex = 5 Else Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub This works great, but only if I am changing time on the second worksheet. I tried to define the cell names on the other worksheets but it either doesn't save or changes the reference to a different sheet, thus making the code only work if I change time on that particular sheet. Can anyone help? I would greatly appreciate it. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
Hi
Names for ranges has to be unique, so therefore if you try to name a range on sheet2 as a name used on sheet1, the first reference will be deleted! If you replace ...Range("TriggerComplete").... with a cell reference ...Range("A1").... it will look at the value in A1 in the active sheet. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range If Not (Intersect(Target, Range(Cells(2, 8), Cells(30, 8))) Is Nothing) Then If ((Sh.Range("A1").Value 0) And (Sh.Range("A2").Value = 0)) Then Sh.Tab.ColorIndex = 3 ElseIf ((Sh.Range("A1").Value 0) And (Sh.Range("A2").Value 0)) Then Sh.Tab.ColorIndex = 5 Else Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub Hopes this helps --- Per On 28 Mar., 03:30, KC_Cheer_Coach wrote: I am using Excel 2003. "KC_Cheer_Coach" wrote: I have an Excel workbook with 49 tabs. Each tab starting with sheet 2 has a list of tasks to be completed. Once each line item is signed and the analyst's time is entered, the line item turns blue (meaning complete). The job cannot be closed until all line items are blued out. The next to the last line item that is completed, is a trigger for the Processor to go in and close the job out and blue the last item. I would like to write something so that when the trigger line item has time entered, the tab turns red, then when the Processor closes the job, the sheet tab turns blue. **Note: The Trigger and Processor line items are not located in the same place on every worksheet. I defined the two time cells as TriggerComplete and ProcessComplete and have placed this code under "This Workbook" : Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range If Not (Intersect(Target, Range(Cells(2, 8), Cells(30, 8))) Is Nothing) Then * * If ((Sh.Range("TriggerComplete").Value 0) And (Sh.Range("ProcessComplete").Value = 0)) Then * * * * Sh.Tab.ColorIndex = 3 * * ElseIf ((Sh.Range("TriggerComplete").Value 0) And (Sh.Range("ProcessorComplete").Value 0)) Then * * * * Sh.Tab.ColorIndex = 5 * * Else * * * * Sh.Tab.ColorIndex = xlColorIndexNone * * End If End If End Sub This works great, but only if I am changing time on the second worksheet. I tried to define the cell names on the other worksheets but it either doesn't save or changes the reference to a different sheet, thus making the code only work if I change time on that particular sheet. Can anyone help? I would greatly appreciate it.- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
Thank you. This works if I put the cell address in, but the cell address
changes. Can the cell address already have a formula in it? If I make a cell change to, lets say D for done when the time is entered for the trigger line item, then can I say something like: If ((Sh.Range(Cells(2, 8), Cells(30, 8)).Value = "D") Or is that not allowed? Everywhere I have looked tells me it can't be done, but I am thinking it has to be possible somehow without copying code into 48 sheets. Thanks! "Per Jessen" wrote: Hi Names for ranges has to be unique, so therefore if you try to name a range on sheet2 as a name used on sheet1, the first reference will be deleted! If you replace ...Range("TriggerComplete").... with a cell reference ...Range("A1").... it will look at the value in A1 in the active sheet. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range If Not (Intersect(Target, Range(Cells(2, 8), Cells(30, 8))) Is Nothing) Then If ((Sh.Range("A1").Value 0) And (Sh.Range("A2").Value = 0)) Then Sh.Tab.ColorIndex = 3 ElseIf ((Sh.Range("A1").Value 0) And (Sh.Range("A2").Value 0)) Then Sh.Tab.ColorIndex = 5 Else Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub Hopes this helps --- Per On 28 Mar., 03:30, KC_Cheer_Coach wrote: I am using Excel 2003. "KC_Cheer_Coach" wrote: I have an Excel workbook with 49 tabs. Each tab starting with sheet 2 has a list of tasks to be completed. Once each line item is signed and the analyst's time is entered, the line item turns blue (meaning complete). The job cannot be closed until all line items are blued out. The next to the last line item that is completed, is a trigger for the Processor to go in and close the job out and blue the last item. I would like to write something so that when the trigger line item has time entered, the tab turns red, then when the Processor closes the job, the sheet tab turns blue. **Note: The Trigger and Processor line items are not located in the same place on every worksheet. I defined the two time cells as TriggerComplete and ProcessComplete and have placed this code under "This Workbook" : Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range If Not (Intersect(Target, Range(Cells(2, 8), Cells(30, 8))) Is Nothing) Then If ((Sh.Range("TriggerComplete").Value 0) And (Sh.Range("ProcessComplete").Value = 0)) Then Sh.Tab.ColorIndex = 3 ElseIf ((Sh.Range("TriggerComplete").Value 0) And (Sh.Range("ProcessorComplete").Value 0)) Then Sh.Tab.ColorIndex = 5 Else Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub This works great, but only if I am changing time on the second worksheet. I tried to define the cell names on the other worksheets but it either doesn't save or changes the reference to a different sheet, thus making the code only work if I change time on that particular sheet. Can anyone help? I would greatly appreciate it.- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
Hi
Sure it's not desireable to paste the code into 48 sheets. You can set up a loop to check if any cell in the range has an D in it. Dim bolTriggerComplete As Boolean For Each cell In sh.Range(Cells(2, 8), Cells(30, 8)) If cell.Value = "D" Then bolTriggerComplete = True Exit Sub End If Next If bolTriggerComplete Then '"D" is found in range 'Do what needs to be done End If Hopes this helps --- Per On 28 Mar., 17:08, KC_Cheer_Coach wrote: Thank you. This works if I put the cell address in, but the cell address changes. Can the cell address already have a formula in it? If I make a cell change to, lets say D for done when the time is entered for the trigger line item, then can I say something like: * * *If ((Sh.Range(Cells(2, 8), Cells(30, 8)).Value = "D") Or is that not allowed? Everywhere I have looked tells me it can't be done, but I am thinking it has to be possible somehow without copying code into 48 sheets. Thanks! "Per Jessen" wrote: Hi Names for ranges has to be unique, so therefore if you try to name a range on sheet2 as a name used on sheet1, the first reference will be deleted! If you replace ...Range("TriggerComplete").... with a cell reference ...Range("A1").... it will look at the value in A1 in the active sheet. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range If Not (Intersect(Target, Range(Cells(2, 8), Cells(30, 8))) Is Nothing) Then * * *If ((Sh.Range("A1").Value 0) And (Sh.Range("A2").Value = 0)) Then * * * * *Sh.Tab.ColorIndex = 3 * * *ElseIf ((Sh.Range("A1").Value 0) And (Sh.Range("A2").Value 0)) Then * * * * *Sh.Tab.ColorIndex = 5 * * *Else * * * * *Sh.Tab.ColorIndex = xlColorIndexNone * * *End If *End If *End Sub Hopes this helps --- Per On 28 Mar., 03:30, KC_Cheer_Coach wrote: I am using Excel 2003. "KC_Cheer_Coach" wrote: I have an Excel workbook with 49 tabs. Each tab starting with sheet 2 has a list of tasks to be completed. Once each line item is signed and the analyst's time is entered, the line item turns blue (meaning complete). The job cannot be closed until all line items are blued out. The next to the last line item that is completed, is a trigger for the Processor to go in and close the job out and blue the last item. I would like to write something so that when the trigger line item has time entered, the tab turns red, then when the Processor closes the job, the sheet tab turns blue. **Note: The Trigger and Processor line items are not located in the same place on every worksheet. I defined the two time cells as TriggerComplete and ProcessComplete and have placed this code under "This Workbook" : Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range If Not (Intersect(Target, Range(Cells(2, 8), Cells(30, 8))) Is Nothing) Then * * If ((Sh.Range("TriggerComplete").Value 0) And (Sh.Range("ProcessComplete").Value = 0)) Then * * * * Sh.Tab.ColorIndex = 3 * * ElseIf ((Sh.Range("TriggerComplete").Value 0) And (Sh.Range("ProcessorComplete").Value 0)) Then * * * * Sh.Tab.ColorIndex = 5 * * Else * * * * Sh.Tab.ColorIndex = xlColorIndexNone * * End If End If End Sub This works great, but only if I am changing time on the second worksheet. I tried to define the cell names on the other worksheets but it either doesn't save or changes the reference to a different sheet, thus making the code only work if I change time on that particular sheet. Can anyone help? I would greatly appreciate it.- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn -- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
Thanks a lot! This worked great but I had some additional conditions so I was
able to take this and tweak it to what I needed. Thanks a million! "Per Jessen" wrote: Hi Sure it's not desireable to paste the code into 48 sheets. You can set up a loop to check if any cell in the range has an D in it. Dim bolTriggerComplete As Boolean For Each cell In sh.Range(Cells(2, 8), Cells(30, 8)) If cell.Value = "D" Then bolTriggerComplete = True Exit Sub End If Next If bolTriggerComplete Then '"D" is found in range 'Do what needs to be done End If Hopes this helps --- Per On 28 Mar., 17:08, KC_Cheer_Coach wrote: Thank you. This works if I put the cell address in, but the cell address changes. Can the cell address already have a formula in it? If I make a cell change to, lets say D for done when the time is entered for the trigger line item, then can I say something like: If ((Sh.Range(Cells(2, 8), Cells(30, 8)).Value = "D") Or is that not allowed? Everywhere I have looked tells me it can't be done, but I am thinking it has to be possible somehow without copying code into 48 sheets. Thanks! "Per Jessen" wrote: Hi Names for ranges has to be unique, so therefore if you try to name a range on sheet2 as a name used on sheet1, the first reference will be deleted! If you replace ...Range("TriggerComplete").... with a cell reference ...Range("A1").... it will look at the value in A1 in the active sheet. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range If Not (Intersect(Target, Range(Cells(2, 8), Cells(30, 8))) Is Nothing) Then If ((Sh.Range("A1").Value 0) And (Sh.Range("A2").Value = 0)) Then Sh.Tab.ColorIndex = 3 ElseIf ((Sh.Range("A1").Value 0) And (Sh.Range("A2").Value 0)) Then Sh.Tab.ColorIndex = 5 Else Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub Hopes this helps --- Per On 28 Mar., 03:30, KC_Cheer_Coach wrote: I am using Excel 2003. "KC_Cheer_Coach" wrote: I have an Excel workbook with 49 tabs. Each tab starting with sheet 2 has a list of tasks to be completed. Once each line item is signed and the analyst's time is entered, the line item turns blue (meaning complete). The job cannot be closed until all line items are blued out. The next to the last line item that is completed, is a trigger for the Processor to go in and close the job out and blue the last item. I would like to write something so that when the trigger line item has time entered, the tab turns red, then when the Processor closes the job, the sheet tab turns blue. **Note: The Trigger and Processor line items are not located in the same place on every worksheet. I defined the two time cells as TriggerComplete and ProcessComplete and have placed this code under "This Workbook" : Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRange As Range If Not (Intersect(Target, Range(Cells(2, 8), Cells(30, 8))) Is Nothing) Then If ((Sh.Range("TriggerComplete").Value 0) And (Sh.Range("ProcessComplete").Value = 0)) Then Sh.Tab.ColorIndex = 3 ElseIf ((Sh.Range("TriggerComplete").Value 0) And (Sh.Range("ProcessorComplete").Value 0)) Then Sh.Tab.ColorIndex = 5 Else Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub This works great, but only if I am changing time on the second worksheet. I tried to define the cell names on the other worksheets but it either doesn't save or changes the reference to a different sheet, thus making the code only work if I change time on that particular sheet. Can anyone help? I would greatly appreciate it.- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn -- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|