Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible for me to have an alert pop-up when I open a docume
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible for me to have an alert pop-up when I open a docume
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible for me to have an alert pop-up when I open a do
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible for me to have an alert pop-up when I open a do
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible for me to have an alert pop-up when I open a do
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible for me to have an alert pop-up when I open a do
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible for me to have an alert pop-up when I open a do
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible for me to have an alert pop-up when I open a do
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible for me to have an alert pop-up when I open a do
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible for me to have an alert pop-up when I open a do
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible for me to have an alert pop-up when I open a do
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible for me to have an alert pop-up when I open a do
Thanks, again, Dave! You guys rock. (Maybe one day I'll actually learn what
all that means, but I thank you greatly for being here to provide it for me.) "Dave Peterson" wrote: 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: Here€„¢s 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 JW€„¢s 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
alert | Excel Discussion (Misc queries) | |||
Alert date | Excel Worksheet Functions | |||
Rookie Alert- | Setting up and Configuration of Excel | |||
How do I suppress a header of footer on the first page of a docume | Setting up and Configuration of Excel | |||
how do I sort email address, dedupes,check for errors in a docume. | Excel Discussion (Misc queries) |