Home |
Search |
Today's Posts |
#1
|
|||
|
|||
User name in Footer
I'm trying to put the user name in a footer and can't figure it out.
I want "This file was prepared by 'username' on &[Date} at &[Time].". But I can't get the username in there. I've tried everything I can think of from system variables to system variables with an & and things that just shouldn't work (and didn't). Any help would be greatly appreciated. |
#2
|
|||
|
|||
Brian,
Try something like ActiveSheet.PageSetup.CenterFooter = "This file was prepared by " & _ Environ("username") & " on &D at &T" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... I'm trying to put the user name in a footer and can't figure it out. I want "This file was prepared by 'username' on &[Date} at &[Time].". But I can't get the username in there. I've tried everything I can think of from system variables to system variables with an & and things that just shouldn't work (and didn't). Any help would be greatly appreciated. |
#3
|
|||
|
|||
Chip,
Thanks for the info, but copying and pasting that didn't work..as I didn't think it would. I don't have any VBA background and I'm presuming that it is VBA. Any other ideas or how can I make this work with my little programming knowledge? Thanks again! "Chip Pearson" wrote: Brian, Try something like ActiveSheet.PageSetup.CenterFooter = "This file was prepared by " & _ Environ("username") & " on &D at &T" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... I'm trying to put the user name in a footer and can't figure it out. I want "This file was prepared by 'username' on &[Date} at &[Time].". But I can't get the username in there. I've tried everything I can think of from system variables to system variables with an & and things that just shouldn't work (and didn't). Any help would be greatly appreciated. |
#4
|
|||
|
|||
Open your workbook.
rightclick on the excel icon at the left of File (in the worksheet menubar). Select view code. Paste this in the code window that opens. Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) Worksheets("sheet1").PageSetup.CenterFooter _ = "This file was prepared by " & Environ("username") & " on &D at &T" End Sub Chip used the Activesheet. I specified just one sheet (sheet1). I'm not sure what you want. Then back to excel and hit the file|print preview button. Brian Allen - McKEOWN INC. wrote: Chip, Thanks for the info, but copying and pasting that didn't work..as I didn't think it would. I don't have any VBA background and I'm presuming that it is VBA. Any other ideas or how can I make this work with my little programming knowledge? Thanks again! "Chip Pearson" wrote: Brian, Try something like ActiveSheet.PageSetup.CenterFooter = "This file was prepared by " & _ Environ("username") & " on &D at &T" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... I'm trying to put the user name in a footer and can't figure it out. I want "This file was prepared by 'username' on &[Date} at &[Time].". But I can't get the username in there. I've tried everything I can think of from system variables to system variables with an & and things that just shouldn't work (and didn't). Any help would be greatly appreciated. -- Dave Peterson |
#5
|
|||
|
|||
Press ALT+F11 to open the VBA Editor, the press CTRL+R to view
the Project Explorer. This will display a tree view window on the left side of the VBA screen. Find your workbook in the tree view, expand that node, and expand the "Microsoft Excel Objects" node. Double click the ThisWorkbook to open that code module. In that code module, paste the following code: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.CenterHeader = "This file was prepared by " & _ Environ("username") & " on &D at &T" End Sub Close the VBA Editor from the File menu. This code will execute automatically when you print the workbook. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... Chip, Thanks for the info, but copying and pasting that didn't work..as I didn't think it would. I don't have any VBA background and I'm presuming that it is VBA. Any other ideas or how can I make this work with my little programming knowledge? Thanks again! "Chip Pearson" wrote: Brian, Try something like ActiveSheet.PageSetup.CenterFooter = "This file was prepared by " & _ Environ("username") & " on &D at &T" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... I'm trying to put the user name in a footer and can't figure it out. I want "This file was prepared by 'username' on &[Date} at &[Time].". But I can't get the username in there. I've tried everything I can think of from system variables to system variables with an & and things that just shouldn't work (and didn't). Any help would be greatly appreciated. |
#6
|
|||
|
|||
Thank you both (Dave and Chip). Much appreciated.
"Chip Pearson" wrote: Press ALT+F11 to open the VBA Editor, the press CTRL+R to view the Project Explorer. This will display a tree view window on the left side of the VBA screen. Find your workbook in the tree view, expand that node, and expand the "Microsoft Excel Objects" node. Double click the ThisWorkbook to open that code module. In that code module, paste the following code: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.CenterHeader = "This file was prepared by " & _ Environ("username") & " on &D at &T" End Sub Close the VBA Editor from the File menu. This code will execute automatically when you print the workbook. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... Chip, Thanks for the info, but copying and pasting that didn't work..as I didn't think it would. I don't have any VBA background and I'm presuming that it is VBA. Any other ideas or how can I make this work with my little programming knowledge? Thanks again! "Chip Pearson" wrote: Brian, Try something like ActiveSheet.PageSetup.CenterFooter = "This file was prepared by " & _ Environ("username") & " on &D at &T" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... I'm trying to put the user name in a footer and can't figure it out. I want "This file was prepared by 'username' on &[Date} at &[Time].". But I can't get the username in there. I've tried everything I can think of from system variables to system variables with an & and things that just shouldn't work (and didn't). Any help would be greatly appreciated. |
#7
|
|||
|
|||
Chip, this worked, but I had to make one change. I had to take the
underscore out. I am using Office 2003, so that may be the difference. Now to see if it works on an Office XP install as is or if XP wants that underscore. Thanks again! "Chip Pearson" wrote: Press ALT+F11 to open the VBA Editor, the press CTRL+R to view the Project Explorer. This will display a tree view window on the left side of the VBA screen. Find your workbook in the tree view, expand that node, and expand the "Microsoft Excel Objects" node. Double click the ThisWorkbook to open that code module. In that code module, paste the following code: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.CenterHeader = "This file was prepared by " & _ Environ("username") & " on &D at &T" End Sub Close the VBA Editor from the File menu. This code will execute automatically when you print the workbook. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... Chip, Thanks for the info, but copying and pasting that didn't work..as I didn't think it would. I don't have any VBA background and I'm presuming that it is VBA. Any other ideas or how can I make this work with my little programming knowledge? Thanks again! "Chip Pearson" wrote: Brian, Try something like ActiveSheet.PageSetup.CenterFooter = "This file was prepared by " & _ Environ("username") & " on &D at &T" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... I'm trying to put the user name in a footer and can't figure it out. I want "This file was prepared by 'username' on &[Date} at &[Time].". But I can't get the username in there. I've tried everything I can think of from system variables to system variables with an & and things that just shouldn't work (and didn't). Any help would be greatly appreciated. |
#8
|
|||
|
|||
The _ character (actually a space followed by a _ ) is a line
continuation character. It lets you write one logical line of VBA code on more than one line in the editor. The code I posted got line wrapped when posting. If you put the entire ActiveSheet.CenterHeader = .... line on one line in the editor, the _ character is not to be used. It makes no difference what version of Excel you are using. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." wrote in message ... Chip, this worked, but I had to make one change. I had to take the underscore out. I am using Office 2003, so that may be the difference. Now to see if it works on an Office XP install as is or if XP wants that underscore. Thanks again! "Chip Pearson" wrote: Press ALT+F11 to open the VBA Editor, the press CTRL+R to view the Project Explorer. This will display a tree view window on the left side of the VBA screen. Find your workbook in the tree view, expand that node, and expand the "Microsoft Excel Objects" node. Double click the ThisWorkbook to open that code module. In that code module, paste the following code: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.CenterHeader = "This file was prepared by " & _ Environ("username") & " on &D at &T" End Sub Close the VBA Editor from the File menu. This code will execute automatically when you print the workbook. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... Chip, Thanks for the info, but copying and pasting that didn't work..as I didn't think it would. I don't have any VBA background and I'm presuming that it is VBA. Any other ideas or how can I make this work with my little programming knowledge? Thanks again! "Chip Pearson" wrote: Brian, Try something like ActiveSheet.PageSetup.CenterFooter = "This file was prepared by " & _ Environ("username") & " on &D at &T" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... I'm trying to put the user name in a footer and can't figure it out. I want "This file was prepared by 'username' on &[Date} at &[Time].". But I can't get the username in there. I've tried everything I can think of from system variables to system variables with an & and things that just shouldn't work (and didn't). Any help would be greatly appreciated. |
#9
|
|||
|
|||
Ok, thanks for the explanation. It worked when I removed it, so I had no
problem. And it works in 2000 and Xp as well. Thanks again! "Chip Pearson" wrote: The _ character (actually a space followed by a _ ) is a line continuation character. It lets you write one logical line of VBA code on more than one line in the editor. The code I posted got line wrapped when posting. If you put the entire ActiveSheet.CenterHeader = .... line on one line in the editor, the _ character is not to be used. It makes no difference what version of Excel you are using. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." wrote in message ... Chip, this worked, but I had to make one change. I had to take the underscore out. I am using Office 2003, so that may be the difference. Now to see if it works on an Office XP install as is or if XP wants that underscore. Thanks again! "Chip Pearson" wrote: Press ALT+F11 to open the VBA Editor, the press CTRL+R to view the Project Explorer. This will display a tree view window on the left side of the VBA screen. Find your workbook in the tree view, expand that node, and expand the "Microsoft Excel Objects" node. Double click the ThisWorkbook to open that code module. In that code module, paste the following code: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.CenterHeader = "This file was prepared by " & _ Environ("username") & " on &D at &T" End Sub Close the VBA Editor from the File menu. This code will execute automatically when you print the workbook. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... Chip, Thanks for the info, but copying and pasting that didn't work..as I didn't think it would. I don't have any VBA background and I'm presuming that it is VBA. Any other ideas or how can I make this work with my little programming knowledge? Thanks again! "Chip Pearson" wrote: Brian, Try something like ActiveSheet.PageSetup.CenterFooter = "This file was prepared by " & _ Environ("username") & " on &D at &T" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian Allen - McKEOWN INC." <Brian Allen - McKEOWN wrote in message ... I'm trying to put the user name in a footer and can't figure it out. I want "This file was prepared by 'username' on &[Date} at &[Time].". But I can't get the username in there. I've tried everything I can think of from system variables to system variables with an & and things that just shouldn't work (and didn't). Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Footer - Ajit | Excel Discussion (Misc queries) | |||
Excel user management | Excel Discussion (Misc queries) | |||
Formatting a footer with a top border-line | Excel Discussion (Misc queries) | |||
File is locked for Editing by user problem | Excel Discussion (Misc queries) | |||
find and match the max | Excel Worksheet Functions |