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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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" <[email protected]> wrote in message
news:unva#[email protected]...
> 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
> >
> >

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" <[email protected]> wrote in message
news:unva#[email protected]...
> 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
> >
> >

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" <[email protected]> wrote in message
> news:unva#[email protected]...
>> 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
>>>
>>>
>
>

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" <[email protected]> wrote in message
> news:unva#[email protected]...
>> 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
>>>
>>>
>
>