Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically update column with weekdays
You could set a flag in the hidden sheet when a change is made there
Private Sub Worksheet_Change(ByVal Target As Range) Thisworkbook.fHidden = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Public fHidden As Boolean and then trap that flag on exit Private Sub Workbook_BeforeClose(Cancel As Boolean) If fHidden Then ThisWorkbook.Save End If End Sub Private Sub Workbook_Open() End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Maddoktor" wrote in message ... Hi all, I have created a workbook that records the user name, date and time the workbook was opened in a hidden worksheet, but as it is updated it asks the user whether to save the workbook of the changes or not before exiting. I would like the workbook to automatically save ONLY the hidden worksheet without offering an option to the user. If the user has modified any other worksheets of the workbook then I would like to follow the protocol of asking the user to save the complete workbook. At all times, the hidden worksheet must be save without question every time the workbook is exited. Is this possible? Thanx in advance Maddoktor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically update column with weekdays
So Sorry Bob,
I sent the wrong e-mail. This e-mail was supposed to be: Column B - to be automatically updated with every date for the month that I enter into B1. i.e. B1 = Feb, then B2=1-Feb, B3=2-Feb, etc ... Column C - to be automatically updated with the serial numbers for each particular date. i.e. B2=1-Feb then C2=4, B3=2-Feb then C3=5, etc ... and finally ... Column A - to be automatically updated with only the dates for which the serial number is greater than 1 and less than 7, i.e. week days. Is this possible. Thanx P.S. Thanx again. It worked great :-) Bob Phillips wrote: You could set a flag in the hidden sheet when a change is made there Private Sub Worksheet_Change(ByVal Target As Range) Thisworkbook.fHidden = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Public fHidden As Boolean and then trap that flag on exit Private Sub Workbook_BeforeClose(Cancel As Boolean) If fHidden Then ThisWorkbook.Save End If End Sub Private Sub Workbook_Open() End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically update column with weekdays
You can do it with formulae. In B2
=IF(ISERROR(DATEVALUE(ROW(A1)&$B$1&YEAR(TODAY()))) ,"",DATEVALUE(ROW(A1)&$B$1 &YEAR(TODAY()))) in C2: =IF(B2<"",WEEKDAY(B2),"") anjd copy down to row 32 -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Maddoktor" wrote in message ... So Sorry Bob, I sent the wrong e-mail. This e-mail was supposed to be: Column B - to be automatically updated with every date for the month that I enter into B1. i.e. B1 = Feb, then B2=1-Feb, B3=2-Feb, etc ... Column C - to be automatically updated with the serial numbers for each particular date. i.e. B2=1-Feb then C2=4, B3=2-Feb then C3=5, etc ... and finally ... Column A - to be automatically updated with only the dates for which the serial number is greater than 1 and less than 7, i.e. week days. Is this possible. Thanx P.S. Thanx again. It worked great :-) Bob Phillips wrote: You could set a flag in the hidden sheet when a change is made there Private Sub Worksheet_Change(ByVal Target As Range) Thisworkbook.fHidden = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Public fHidden As Boolean and then trap that flag on exit Private Sub Workbook_BeforeClose(Cancel As Boolean) If fHidden Then ThisWorkbook.Save End If End Sub Private Sub Workbook_Open() End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically update column with weekdays
BTW your clock is 3 days ahead, please reset as it distorts the posts in the
NG. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Maddoktor" wrote in message ... So Sorry Bob, I sent the wrong e-mail. This e-mail was supposed to be: Column B - to be automatically updated with every date for the month that I enter into B1. i.e. B1 = Feb, then B2=1-Feb, B3=2-Feb, etc ... Column C - to be automatically updated with the serial numbers for each particular date. i.e. B2=1-Feb then C2=4, B3=2-Feb then C3=5, etc ... and finally ... Column A - to be automatically updated with only the dates for which the serial number is greater than 1 and less than 7, i.e. week days. Is this possible. Thanx P.S. Thanx again. It worked great :-) Bob Phillips wrote: You could set a flag in the hidden sheet when a change is made there Private Sub Worksheet_Change(ByVal Target As Range) Thisworkbook.fHidden = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Public fHidden As Boolean and then trap that flag on exit Private Sub Workbook_BeforeClose(Cancel As Boolean) If fHidden Then ThisWorkbook.Save End If End Sub Private Sub Workbook_Open() End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically update column with weekdays
Sorry about the clock Bob.
Bob Phillips wrote: BTW your clock is 3 days ahead, please reset as it distorts the posts in the NG. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Maddoktor" wrote in message ... So Sorry Bob, I sent the wrong e-mail. This e-mail was supposed to be: Column B - to be automatically updated with every date for the month that I enter into B1. i.e. B1 = Feb, then B2=1-Feb, B3=2-Feb, etc ... Column C - to be automatically updated with the serial numbers for each particular date. i.e. B2=1-Feb then C2=4, B3=2-Feb then C3=5, etc ... and finally ... Column A - to be automatically updated with only the dates for which the serial number is greater than 1 and less than 7, i.e. week days. Is this possible. Thanx P.S. Thanx again. It worked great :-) Bob Phillips wrote: You could set a flag in the hidden sheet when a change is made there Private Sub Worksheet_Change(ByVal Target As Range) Thisworkbook.fHidden = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Public fHidden As Boolean and then trap that flag on exit Private Sub Workbook_BeforeClose(Cancel As Boolean) If fHidden Then ThisWorkbook.Save End If End Sub Private Sub Workbook_Open() End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically update column with weekdays
Hi all,
I have created a workbook that records the user name, date and time the workbook was opened in a hidden worksheet, but as it is updated it asks the user whether to save the workbook of the changes or not before exiting. I would like the workbook to automatically save ONLY the hidden worksheet without offering an option to the user. If the user has modified any other worksheets of the workbook then I would like to follow the protocol of asking the user to save the complete workbook. At all times, the hidden worksheet must be save without question every time the workbook is exited. Is this possible? Thanx in advance Maddoktor |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically update column with weekdays
Bob,
I have the month of "January" in cell D75, and have entered the formulas from your last reply in L82 and M82 and all I get in L82 is "2006", column M has the serial number 4 and column A doesn't automatically populate with every date with serial numbers greater than or equal to 2 and less than or equal to 6. Can you please help. I am not sure what I am doing wrong. Bob Phillips wrote: You can do it with formulae. In B2 =IF(ISERROR(DATEVALUE(ROW(A1)&$B$1&YEAR(TODAY()))) ,"",DATEVALUE(ROW(A1)&$B$1 &YEAR(TODAY()))) in C2: =IF(B2<"",WEEKDAY(B2),"") anjd copy down to row 32 -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Maddoktor" wrote in message ... So Sorry Bob, I sent the wrong e-mail. This e-mail was supposed to be: Column B - to be automatically updated with every date for the month that I enter into B1. i.e. B1 = Feb, then B2=1-Feb, B3=2-Feb, etc ... Column C - to be automatically updated with the serial numbers for each particular date. i.e. B2=1-Feb then C2=4, B3=2-Feb then C3=5, etc ... and finally ... Column A - to be automatically updated with only the dates for which the serial number is greater than 1 and less than 7, i.e. week days. Is this possible. Thanx P.S. Thanx again. It worked great :-) Bob Phillips wrote: You could set a flag in the hidden sheet when a change is made there Private Sub Worksheet_Change(ByVal Target As Range) Thisworkbook.fHidden = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Public fHidden As Boolean and then trap that flag on exit Private Sub Workbook_BeforeClose(Cancel As Boolean) If fHidden Then ThisWorkbook.Save End If End Sub Private Sub Workbook_Open() End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column Chart Update automatically for all worksheets? | Charts and Charting in Excel | |||
Creating a year of weekdays automatically? | Excel Programming | |||
Automatically update column - Autofill | Excel Programming | |||
Automatically update column - Autofill | Excel Programming | |||
Automatically update column - Autofill | Excel Programming |