I have an EXCEL document that requires daily updating and weekly reprinting.
There's probably a better way to format what I need, but I don't know how to
find it. I tried googling but I don't really even know how to word it.
Please bear with me as I am new to EXCEL. (These MS Newsgroups helped me
greatly several years ago when I was new to Word and Access; alas I don't use
Access in this job.) I have the date as a header item, in the following
format: "Week of October 8 - 12, 2007". Since I'm not the only person who
uses this item, I would like a reminder box to pop-up when this is opened,
saying what the date is and asking the user if they would like to change this
date or leave it as it is. (I.e., if my assistant goes to open it on Thursday
(3 days from now), the box would pop up and it would read "The date in the
header is for the week of October 8 - 12, 2007. If you would like to change
this date, please do so now.") Is this possible? Thanks for all your help.

In the ThisWorkbook module of the workbook, place this:
Private Sub Workbook_Open()
MsgBox "The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "If you would " & _
"like to change this date, please do so now"
End Sub
This will display a popup message stating the info from the center
header of the activesheet when the workbook opens. If you need to
ensure that a particular sheet is the one that it opens to, you can
add an activate line before the msgbox line:
Sheets("your_sheet_name").Activate

You could take this a step further and automate the the process a
little more. No error handling in the below routine, but it's just to
give you an idea of what could be done.
Private Sub Workbook_Open()
If MsgBox("The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "Would you like " & _
"like to change this date now?", _
vbYesNo, "Change Header") = vbYes Then
newDate = InputBox("Enter the new date", _
"Entry")
ActiveSheet.PageSetup.CenterHeader = newDate
End If
End Sub



Max wrote:
I have an EXCEL document that requires daily updating and weekly reprinting.
There's probably a better way to format what I need, but I don't know how to
find it. I tried googling but I don't really even know how to word it.
Please bear with me as I am new to EXCEL. (These MS Newsgroups helped me
greatly several years ago when I was new to Word and Access; alas I don't use
Access in this job.) I have the date as a header item, in the following
format: "Week of October 8 - 12, 2007". Since I'm not the only person who
uses this item, I would like a reminder box to pop-up when this is opened,
saying what the date is and asking the user if they would like to change this
date or leave it as it is. (I.e., if my assistant goes to open it on Thursday
(3 days from now), the box would pop up and it would read "The date in the
header is for the week of October 8 - 12, 2007. If you would like to change
this date, please do so now.") Is this possible? Thanks for all your help.

Thank you! This actually looks a bit more "techy" than I think I can handle,
but I'm going to give it that good ol' college try anyway. I can create it
under a "practice" filename and try it there and if I do something wrong, I
can just trash it and try again! Thanks again.

"JW" wrote:

In the ThisWorkbook module of the workbook, place this:
Private Sub Workbook_Open()
MsgBox "The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "If you would " & _
"like to change this date, please do so now"
End Sub
This will display a popup message stating the info from the center
header of the activesheet when the workbook opens. If you need to
ensure that a particular sheet is the one that it opens to, you can
add an activate line before the msgbox line:
Sheets("your_sheet_name").Activate

You could take this a step further and automate the the process a
little more. No error handling in the below routine, but it's just to
give you an idea of what could be done.
Private Sub Workbook_Open()
If MsgBox("The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "Would you like " & _
"like to change this date now?", _
vbYesNo, "Change Header") = vbYes Then
newDate = InputBox("Enter the new date", _
"Entry")
ActiveSheet.PageSetup.CenterHeader = newDate
End If
End Sub



Max wrote:
I have an EXCEL document that requires daily updating and weekly reprinting.
There's probably a better way to format what I need, but I don't know how to
find it. I tried googling but I don't really even know how to word it.
Please bear with me as I am new to EXCEL. (These MS Newsgroups helped me
greatly several years ago when I was new to Word and Access; alas I don't use
Access in this job.) I have the date as a header item, in the following
format: "Week of October 8 - 12, 2007". Since I'm not the only person who
uses this item, I would like a reminder box to pop-up when this is opened,
saying what the date is and asking the user if they would like to change this
date or leave it as it is. (I.e., if my assistant goes to open it on Thursday
(3 days from now), the box would pop up and it would read "The date in the
header is for the week of October 8 - 12, 2007. If you would like to change
this date, please do so now.") Is this possible? Thanks for all your help.

JW, this is giving me some grief. Can I simply copy & paste the code?

"JW" wrote:

In the ThisWorkbook module of the workbook, place this:
Private Sub Workbook_Open()
MsgBox "The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "If you would " & _
"like to change this date, please do so now"
End Sub
This will display a popup message stating the info from the center
header of the activesheet when the workbook opens. If you need to
ensure that a particular sheet is the one that it opens to, you can
add an activate line before the msgbox line:
Sheets("your_sheet_name").Activate

You could take this a step further and automate the the process a
little more. No error handling in the below routine, but it's just to
give you an idea of what could be done.
Private Sub Workbook_Open()
If MsgBox("The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "Would you like " & _
"like to change this date now?", _
vbYesNo, "Change Header") = vbYes Then
newDate = InputBox("Enter the new date", _
"Entry")
ActiveSheet.PageSetup.CenterHeader = newDate
End If
End Sub



Max wrote:
I have an EXCEL document that requires daily updating and weekly reprinting.
There's probably a better way to format what I need, but I don't know how to
find it. I tried googling but I don't really even know how to word it.
Please bear with me as I am new to EXCEL. (These MS Newsgroups helped me
greatly several years ago when I was new to Word and Access; alas I don't use
Access in this job.) I have the date as a header item, in the following
format: "Week of October 8 - 12, 2007". Since I'm not the only person who
uses this item, I would like a reminder box to pop-up when this is opened,
saying what the date is and asking the user if they would like to change this
date or leave it as it is. (I.e., if my assistant goes to open it on Thursday
(3 days from now), the box would pop up and it would read "The date in the
header is for the week of October 8 - 12, 2007. If you would like to change
this date, please do so now.") Is this possible? Thanks for all your help.

Did you remember to put the code under the ThisWorkbook module -- not in a
General module?

And did you remember to allow macros to run?

If yes to both, what kind of grief are you getting?

Max wrote:

JW, this is giving me some grief. Can I simply copy & paste the code?

"JW" wrote:

In the ThisWorkbook module of the workbook, place this:
Private Sub Workbook_Open()
MsgBox "The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "If you would " & _
"like to change this date, please do so now"
End Sub
This will display a popup message stating the info from the center
header of the activesheet when the workbook opens. If you need to
ensure that a particular sheet is the one that it opens to, you can
add an activate line before the msgbox line:
Sheets("your_sheet_name").Activate

You could take this a step further and automate the the process a
little more. No error handling in the below routine, but it's just to
give you an idea of what could be done.
Private Sub Workbook_Open()
If MsgBox("The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "Would you like " & _
"like to change this date now?", _
vbYesNo, "Change Header") = vbYes Then
newDate = InputBox("Enter the new date", _
"Entry")
ActiveSheet.PageSetup.CenterHeader = newDate
End If
End Sub



Max wrote:
I have an EXCEL document that requires daily updating and weekly reprinting.
There's probably a better way to format what I need, but I don't know how to
find it. I tried googling but I don't really even know how to word it.
Please bear with me as I am new to EXCEL. (These MS Newsgroups helped me
greatly several years ago when I was new to Word and Access; alas I don't use
Access in this job.) I have the date as a header item, in the following
format: "Week of October 8 - 12, 2007". Since I'm not the only person who
uses this item, I would like a reminder box to pop-up when this is opened,
saying what the date is and asking the user if they would like to change this
date or leave it as it is. (I.e., if my assistant goes to open it on Thursday
(3 days from now), the box would pop up and it would read "The date in the
header is for the week of October 8 - 12, 2007. If you would like to change
this date, please do so now.") Is this possible? Thanks for all your help.



--

Dave Peterson

I did warn you that I'm new to Excel & that this is a bit too technical to
me. Even though I don't know what the terms mean, I found the Module menu
and determined that there's General & ThisWorkbook, + chose ThisWorkbook,
copied and pasted the code from here (from the first half of JW's reply);
closed that window, closed Excel saving the changes. When I went to reopen
it, I got a pop-up alert saying "Macros are disabled because the security
level is set to High and a digitally signed Trusted Certificate is not
attached to the macros. To run the macros, change the security level to a
lower setting (not recommended), or request the macros be signed by the
author using a certificate issued by a Certificate Autority."

I see that there are 3 worksheets on this when it's open, but I only use
what's on Worksheet 1; I don't really know what a worksheet is! I think that
what JW gave me is computer code, but I don't know a thing about that. I
also know zilch about macros, let alone Certificates and/or Certificate
Authorization. I don't know what to do next. Can you help me? Or can you
tell me how to find the answers I need?

FWIW, I am taking an Intro. to Excel workshop at the end of this month,
hopefully the instructors will let us work on real-job situations like this.
I would really like to learn Excel.

"Max" wrote:

JW, this is giving me some grief. Can I simply copy & paste the code?

"JW" wrote:

In the ThisWorkbook module of the workbook, place this:
Private Sub Workbook_Open()
MsgBox "The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "If you would " & _
"like to change this date, please do so now"
End Sub
This will display a popup message stating the info from the center
header of the activesheet when the workbook opens. If you need to
ensure that a particular sheet is the one that it opens to, you can
add an activate line before the msgbox line:
Sheets("your_sheet_name").Activate

You could take this a step further and automate the the process a
little more. No error handling in the below routine, but it's just to
give you an idea of what could be done.
Private Sub Workbook_Open()
If MsgBox("The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "Would you like " & _
"like to change this date now?", _
vbYesNo, "Change Header") = vbYes Then
newDate = InputBox("Enter the new date", _
"Entry")
ActiveSheet.PageSetup.CenterHeader = newDate
End If
End Sub



Max wrote:
I have an EXCEL document that requires daily updating and weekly reprinting.
There's probably a better way to format what I need, but I don't know how to
find it. I tried googling but I don't really even know how to word it.
Please bear with me as I am new to EXCEL. (These MS Newsgroups helped me
greatly several years ago when I was new to Word and Access; alas I don't use
Access in this job.) I have the date as a header item, in the following
format: "Week of October 8 - 12, 2007". Since I'm not the only person who
uses this item, I would like a reminder box to pop-up when this is opened,
saying what the date is and asking the user if they would like to change this
date or leave it as it is. (I.e., if my assistant goes to open it on Thursday
(3 days from now), the box would pop up and it would read "The date in the
header is for the week of October 8 - 12, 2007. If you would like to change
this date, please do so now.") Is this possible? Thanks for all your help.

Max

Go to ToolsOptionsSecurityMacro Security and change to "Medium"

When you open the workbook you will be asked if you want to enable macros.

Click "Enable" and the workbook will open and the code will run.


Gord Dibben MS Excel MVP

On Tue, 9 Oct 2007 11:44:01 -0700, Max wrote:

I did warn you that I'm new to Excel & that this is a bit too technical to
me. Even though I don't know what the terms mean, I found the Module menu
and determined that there's General & ThisWorkbook, + chose ThisWorkbook,
copied and pasted the code from here (from the first half of JW's reply);
closed that window, closed Excel saving the changes. When I went to reopen
it, I got a pop-up alert saying "Macros are disabled because the security
level is set to High and a digitally signed Trusted Certificate is not
attached to the macros. To run the macros, change the security level to a
lower setting (not recommended), or request the macros be signed by the
author using a certificate issued by a Certificate Autority."

I see that there are 3 worksheets on this when it's open, but I only use
what's on Worksheet 1; I don't really know what a worksheet is! I think that
what JW gave me is computer code, but I don't know a thing about that. I
also know zilch about macros, let alone Certificates and/or Certificate
Authorization. I don't know what to do next. Can you help me? Or can you
tell me how to find the answers I need?

FWIW, I am taking an Intro. to Excel workshop at the end of this month,
hopefully the instructors will let us work on real-job situations like this.
I would really like to learn Excel.

"Max" wrote:

JW, this is giving me some grief. Can I simply copy & paste the code?

"JW" wrote:

In the ThisWorkbook module of the workbook, place this:
Private Sub Workbook_Open()
MsgBox "The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "If you would " & _
"like to change this date, please do so now"
End Sub
This will display a popup message stating the info from the center
header of the activesheet when the workbook opens. If you need to
ensure that a particular sheet is the one that it opens to, you can
add an activate line before the msgbox line:
Sheets("your_sheet_name").Activate

You could take this a step further and automate the the process a
little more. No error handling in the below routine, but it's just to
give you an idea of what could be done.
Private Sub Workbook_Open()
If MsgBox("The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "Would you like " & _
"like to change this date now?", _
vbYesNo, "Change Header") = vbYes Then
newDate = InputBox("Enter the new date", _
"Entry")
ActiveSheet.PageSetup.CenterHeader = newDate
End If
End Sub



Max wrote:
I have an EXCEL document that requires daily updating and weekly reprinting.
There's probably a better way to format what I need, but I don't know how to
find it. I tried googling but I don't really even know how to word it.
Please bear with me as I am new to EXCEL. (These MS Newsgroups helped me
greatly several years ago when I was new to Word and Access; alas I don't use
Access in this job.) I have the date as a header item, in the following
format: "Week of October 8 - 12, 2007". Since I'm not the only person who
uses this item, I would like a reminder box to pop-up when this is opened,
saying what the date is and asking the user if they would like to change this
date or leave it as it is. (I.e., if my assistant goes to open it on Thursday
(3 days from now), the box would pop up and it would read "The date in the
header is for the week of October 8 - 12, 2007. If you would like to change
this date, please do so now.") Is this possible? Thanks for all your help.

Woohoo, kewl! I love learning new stuff especially when it works. Thank you
so much, you guys, IMO you're ALL MVP's.

Now I have one tiny little problem and 1 more question though. The pop-up
alert says the following: "The date in the header is &"Arial,Bold" &20Week of
October 15 - 19, 2007 Page &P

If you would like to change this date, please do so now."

I don't want the user to see those format codes, how do I delete that
&"Arial,Bold" &20 bit?

My last question, I hope, is: That macro means that the pop-up message will
reflect whichever date is typed into that header, right?

"Gord Dibben" wrote:

Max

Go to ToolsOptionsSecurityMacro Security and change to "Medium"

When you open the workbook you will be asked if you want to enable macros.

Click "Enable" and the workbook will open and the code will run.


Gord Dibben MS Excel MVP

On Tue, 9 Oct 2007 11:44:01 -0700, Max wrote:

I did warn you that I'm new to Excel & that this is a bit too technical to
me. Even though I don't know what the terms mean, I found the Module menu
and determined that there's General & ThisWorkbook, + chose ThisWorkbook,
copied and pasted the code from here (from the first half of JW's reply);
closed that window, closed Excel saving the changes. When I went to reopen
it, I got a pop-up alert saying "Macros are disabled because the security
level is set to High and a digitally signed Trusted Certificate is not
attached to the macros. To run the macros, change the security level to a
lower setting (not recommended), or request the macros be signed by the
author using a certificate issued by a Certificate Autority."

I see that there are 3 worksheets on this when it's open, but I only use
what's on Worksheet 1; I don't really know what a worksheet is! I think that
what JW gave me is computer code, but I don't know a thing about that. I
also know zilch about macros, let alone Certificates and/or Certificate
Authorization. I don't know what to do next. Can you help me? Or can you
tell me how to find the answers I need?

FWIW, I am taking an Intro. to Excel workshop at the end of this month,
hopefully the instructors will let us work on real-job situations like this.
I would really like to learn Excel.

"Max" wrote:

JW, this is giving me some grief. Can I simply copy & paste the code?

"JW" wrote:

In the ThisWorkbook module of the workbook, place this:
Private Sub Workbook_Open()
MsgBox "The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "If you would " & _
"like to change this date, please do so now"
End Sub
This will display a popup message stating the info from the center
header of the activesheet when the workbook opens. If you need to
ensure that a particular sheet is the one that it opens to, you can
add an activate line before the msgbox line:
Sheets("your_sheet_name").Activate

You could take this a step further and automate the the process a
little more. No error handling in the below routine, but it's just to
give you an idea of what could be done.
Private Sub Workbook_Open()
If MsgBox("The date in the header is " & _
ActiveSheet.PageSetup.CenterHeader & _
Chr(10) & Chr(10) & "Would you like " & _
"like to change this date now?", _
vbYesNo, "Change Header") = vbYes Then
newDate = InputBox("Enter the new date", _
"Entry")
ActiveSheet.PageSetup.CenterHeader = newDate
End If
End Sub



Max wrote:
I have an EXCEL document that requires daily updating and weekly reprinting.
There's probably a better way to format what I need, but I don't know how to
find it. I tried googling but I don't really even know how to word it.
Please bear with me as I am new to EXCEL. (These MS Newsgroups helped me
greatly several years ago when I was new to Word and Access; alas I don't use
Access in this job.) I have the date as a header item, in the following
format: "Week of October 8 - 12, 2007". Since I'm not the only person who
uses this item, I would like a reminder box to pop-up when this is opened,
saying what the date is and asking the user if they would like to change this
date or leave it as it is. (I.e., if my assistant goes to open it on Thursday
(3 days from now), the box would pop up and it would read "The date in the
header is for the week of October 8 - 12, 2007. If you would like to change
this date, please do so now.") Is this possible? Thanks for all your help.