As long as you know that those strings are always included, you can look for
them and clean up the whole header.

This replaces the original code:

Option Explicit
Private Sub Workbook_Open()

Dim myHeader As String
Dim WeekOfPos As Long
Dim PagePos As Long
Dim TheWeekOfStr As String
Dim PageStr As String
Dim myStr As String

TheWeekOfStr = "week of "
PageStr = "page &P"

myHeader = ActiveSheet.PageSetup.CenterHeader

WeekOfPos = InStr(1, myHeader, TheWeekOfStr, vbTextCompare)
If WeekOfPos 0 Then
'it was found
WeekOfPos = WeekOfPos + Len(TheWeekOfStr)
End If

PagePos = InStr(1, myHeader, PageStr, vbTextCompare)

If WeekOfPos 0 _
And PagePos 0 Then
myStr = Left(myHeader, PagePos - 1)
myStr = Mid(myStr, WeekOfPos)
myStr = Trim(myStr)
Else
myStr = myHeader
End If

MsgBox "The date in the header is:" & vbLf & _
myStr & _
vbLf & vbLf & "If you would " & _
"like to change this date, please do so now"
End Sub



Max wrote:

Heres a recap.

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.")

I used this code from JW:
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"

After I enabled macros and changed the security level, as per Dave Peterson,
I got almost what I wanted, except that the pop-up box contains the font
formatting code preceding the actual date. 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 aim is to have a pop-up alert to 1, let the user know what date is in the
header the last time the item was saved, and 2, remind them to change the
date if appropriate.

OR use JWs second set of code which I think is to enable the date to be
entered while in the alert dialog box.

"Gord Dibben" wrote:

Best you post which set of code you used and any edits you may have made.

I can't tell which you are using.

You want to have a message and a method to change the date range in the header,
right?


Gord

On Tue, 9 Oct 2007 12:28:03 -0700, Max wrote:

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.







--

Dave Peterson