Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
52 Work sheets (Tabs)
I have 52 work sheets named week 1 to week 2 once data is completed for each
week is it possible to have the tabs change colour automatically to indicate week is finished to go on to next week if it is HOW |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
52 Work sheets (Tabs)
Excel needs to know when you have completed a sheet. Say we pick B9. If you
enter a value in B9 Excel will do the coloring. Enter this event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub ActiveSheet.Tab.ColorIndex = 10 End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm Repeat for each worksheet. -- Gary''s Student - gsnu200908 "Mike" wrote: I have 52 work sheets named week 1 to week 2 once data is completed for each week is it possible to have the tabs change colour automatically to indicate week is finished to go on to next week if it is HOW |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
52 Work sheets (Tabs)
Its not working i pasted this in & closed it like you asked i am using cell
B300 for each sheet 1 to 52 & i want to change colour of tabs from blue to green once B300 is filled "Gary''s Student" wrote: Excel needs to know when you have completed a sheet. Say we pick B9. If you enter a value in B9 Excel will do the coloring. Enter this event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub ActiveSheet.Tab.ColorIndex = 10 End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm Repeat for each worksheet. -- Gary''s Student - gsnu200908 "Mike" wrote: I have 52 work sheets named week 1 to week 2 once data is completed for each week is it possible to have the tabs change colour automatically to indicate week is finished to go on to next week if it is HOW |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
52 Work sheets (Tabs)
Copy/paste into Thisworkbook module.
Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) If Intersect(Target, Range("B300")) Is Nothing Then Exit Sub ActiveSheet.Tab.ColorIndex = 10 End Sub Will work on whichever sheet is active and whenever you manually fill B300 on that sheet. Gord Dibben MS Excel MVP On Fri, 20 Nov 2009 09:06:03 -0800, Mike wrote: Its not working i pasted this in & closed it like you asked i am using cell B300 for each sheet 1 to 52 & i want to change colour of tabs from blue to green once B300 is filled "Gary''s Student" wrote: Excel needs to know when you have completed a sheet. Say we pick B9. If you enter a value in B9 Excel will do the coloring. Enter this event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub ActiveSheet.Tab.ColorIndex = 10 End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm Repeat for each worksheet. -- Gary''s Student - gsnu200908 "Mike" wrote: I have 52 work sheets named week 1 to week 2 once data is completed for each week is it possible to have the tabs change colour automatically to indicate week is finished to go on to next week if it is HOW |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
52 Work sheets (Tabs)
This Worked Thank you !! One more question I have a Template for these 52
Weeks if i want to change something in Template is it possible to have the 52 other sheets change as well through Macro,s if possible How "Gord Dibben" wrote: Copy/paste into Thisworkbook module. Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) If Intersect(Target, Range("B300")) Is Nothing Then Exit Sub ActiveSheet.Tab.ColorIndex = 10 End Sub Will work on whichever sheet is active and whenever you manually fill B300 on that sheet. Gord Dibben MS Excel MVP On Fri, 20 Nov 2009 09:06:03 -0800, Mike wrote: Its not working i pasted this in & closed it like you asked i am using cell B300 for each sheet 1 to 52 & i want to change colour of tabs from blue to green once B300 is filled "Gary''s Student" wrote: Excel needs to know when you have completed a sheet. Say we pick B9. If you enter a value in B9 Excel will do the coloring. Enter this event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub ActiveSheet.Tab.ColorIndex = 10 End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm Repeat for each worksheet. -- Gary''s Student - gsnu200908 "Mike" wrote: I have 52 work sheets named week 1 to week 2 once data is completed for each week is it possible to have the tabs change colour automatically to indicate week is finished to go on to next week if it is HOW . |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
52 Work sheets (Tabs)
Is the Template sheet within the workbook?
What type of changes need to be made to Template sheet that would ripple through the 52 week sheets? Probably the easiest method is................... Manually you can select Template sheet first then Shift + click on last sheet to group all 53 sheets. Make changes to Template sheet and will be done to all sheets in the group. DO NOT FORGET to ungroup the sheets when done with your changes. Gord On Sat, 21 Nov 2009 07:53:01 -0800, Mike wrote: This Worked Thank you !! One more question I have a Template for these 52 Weeks if i want to change something in Template is it possible to have the 52 other sheets change as well through Macro,s if possible How "Gord Dibben" wrote: Copy/paste into Thisworkbook module. Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) If Intersect(Target, Range("B300")) Is Nothing Then Exit Sub ActiveSheet.Tab.ColorIndex = 10 End Sub Will work on whichever sheet is active and whenever you manually fill B300 on that sheet. Gord Dibben MS Excel MVP On Fri, 20 Nov 2009 09:06:03 -0800, Mike wrote: Its not working i pasted this in & closed it like you asked i am using cell B300 for each sheet 1 to 52 & i want to change colour of tabs from blue to green once B300 is filled "Gary''s Student" wrote: Excel needs to know when you have completed a sheet. Say we pick B9. If you enter a value in B9 Excel will do the coloring. Enter this event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub ActiveSheet.Tab.ColorIndex = 10 End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm Repeat for each worksheet. -- Gary''s Student - gsnu200908 "Mike" wrote: I have 52 work sheets named week 1 to week 2 once data is completed for each week is it possible to have the tabs change colour automatically to indicate week is finished to go on to next week if it is HOW . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
right click does not work to tabs | Excel Discussion (Misc queries) | |||
no sheet tabs on work sheets | Excel Discussion (Misc queries) | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
Number of worksheet tabs as cell value? ie 4 work sheets = 4 cell value | Excel Discussion (Misc queries) | |||
I wish to save my Excell work in my work sheets | Excel Worksheet Functions |