Prompt For Print Footer On Open

Aug 23, 2007

When a user Opens this particular Workbook I want them to put their name into an Input Box so that it appears on the Footer of each sheet. I have several Sheets in this particular Workbook. If they do not put there name in I want the workbook to close. I am having problems with the code I am using. Please see below:

Private Sub Workbook_Open()
Dim strName As String
strName = InputBox(Prompt:="Your Name Please.", _
Title:="Please Enter Your Name", Default:="Enter Name Here")

If strName = "Enter Name Here" Or _
strName = vbNullString Then
Exit Sub
Else: strName = vbNotNullString
Worksheets("Front Page").RightFooter = strName
End If
End Sub

As you can see I still need a code after the first IF statement to close workbook. My main issue is after the else.

View 2 Replies


ADVERTISEMENT

Before Printing Workbooks: Prompt For Footer/Header

Oct 11, 2006

Before printing ANY workbook, I want the user to have the option of adding a footer.

To start out with, here is the code I have:

Sub Workbook_BeforePrint(Cancel As Boolean)
'When you click 'Print', this module asks if you want to add a footer.
'The one it adds by default is: Path & filename, worksheet name, date.
'Unfortunately custom button text is not available, so Yes means add to
'current sheet, No means no, and Cancel means add to all sheets in workbook.

Dim answer$
Dim sheet As Worksheet

If ActiveSheet.PageSetup.LeftFooter = "" Then 'if there isn't a footer already
answer = MsgBox("Do you want to add a footer?" & vbCrLf & "Yes - this sheet only, Cancel - all sheets", vbYesNoCancel)

This works just fine if I put it in the ThisWorkbook section of a workbook. The problem is I can't get to work for any old workbook. If I put it in personal.xls, nothing happens when the user clicks print.

View 6 Replies View Related

Print Username In Footer?

Nov 26, 2012

I would like the name of the User to print in the Footer in Excel.

View 9 Replies View Related

Print Footer On Last Page Only - VBA?

Jul 21, 2013

I am working on workbook having more then 15 sheets and i want to print the cell reference (L3:P14) as footer automatically but only on the last page printed.

View 2 Replies View Related

Force Specified Footer In Print

Aug 14, 2006

Need to write VBA for inserting Footer for each page based on Print command. My object is whenever any one is taking the print from this file my specific footer should get printed. The user should not be allowed to change it.

View 4 Replies View Related

Variable In Footer For Print Macro

Sep 5, 2006

This sounds like a daft question I know but I have searched the forum but not found an answer despite finding a lot about footers. How do I include a formatted variable value in my printed footer? I have a function that gives me the file creation date and I need that in the footer but formatted 8pt Arial Bold. I have no problem inserting a formatted fixed string.

View 9 Replies View Related

Print Footer Rows At The Bottom Of Every Page

Jun 1, 2013

I have a TAB Named: Sheet18. I already set up the Header rows to print at the top of every page. Now I need to know how to set up to print the Footer at the Bottom of every-page the footer is A4804:Z4806, i need this rows printed at the bottom of every page when i print.

View 1 Replies View Related

Update Print Header And Footer With VBA Code

Jun 22, 2005

I am trying to link what is in the header and footer with information contained in certain Cells. (I eventually want to have a user form to help input the information, but one thing at a time.)

How do I go about getting the following information into code so that I can update the enclosed code?

Left Header: Client Name contained in Sheet: "Table of Contents" B:1

View 5 Replies View Related

Change Font Color In Print Footer

Jun 10, 2008

I wrote a macro to do me page footers

Sub EditFooter()
With ActiveSheet
.PageSetup.CenterFooter = "Page &P of &N"
.PageSetup.RightFooter = "Préparé le " & Format( Date, "dddd, dd MMMM yyyy")
End With
End Sub

it works fine, howver I would like to change the font color to a light grey, and I have no clue how to do that.

View 3 Replies View Related

Print Button Macro Prompt?

Mar 23, 2014

I have a print button or object and I want to assign a macro to it that when clicked brings up a prompt to type in a Month or Year to Date and prints the data that corresponds to that month or YTD. I have a Date column in a table that I can get a different macro to print by basially having the macro filter by blanks, so essentially printing all the dates in the table, but I feel as if it could be much better and simpler by prompting the macro to filter by "x" Month and print.

View 3 Replies View Related

Prompt Open Closed Workbook Then Copy Range And Paste Special Into Open Workbook

Apr 6, 2013

I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.

I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.

I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need

A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)

I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.

week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost

1
2
3
4
5
6
7
8

View 7 Replies View Related

Prompt File Open

Jan 25, 2007

how can I have a prompt and input box that I can put the name of the file and it would open the sheet.

View 6 Replies View Related

Prompt User To Open Sheet As Macro Enabled - VBA

Jun 19, 2013

I have got a protected sheet with macros, how can I ensure that users can only open the sheet as Macro enabled only otherwise the sheet would not open?

I understand that some users may have different Macro security settings?

View 1 Replies View Related

Suppress Open File Prompt When Copying Over Formula Referencing Closed Workbooks?

Dec 13, 2013

I have an Excel file that contains formulas that reference external workbooks on a shared network. Each month, I copy the column of formulas over to the next month's column. Then, to update the file path, I highlight the new column and do a Find-and-Replace, swapping the previous month's name for the current month. This practice works fine. The only thing is ... the "Open File" promptbox appears for each formula where I updated the file path (i.e. and this can be hundreds instances!). I wind-up having to choose the file from the exact same file path. It is an unnecessary, and annoying extra step to take.

View 2 Replies View Related

Excel 2007 :: Macro To Open A Folder Then Prompt To Allow Manual Selection Of File?

Sep 9, 2013

Its been a while since I did this on 2003 and needing a pointer on 2007.

Just needing a simple macro that opens the relevant folder from a hardcoded path to allow the user to manually select the file to be used.

This file will then be used to copy from and paste to another file.

View 8 Replies View Related

Open Print Dialog Box

Feb 23, 2010

I would like a macro to print 2 sheets in a workbook. I can write the macro that will select the sheets I want, but I don't want it to just go and print because depending on if the user wants it printed in color, b&w, etc.

they have to choose a different color. What code do I use to open the print dialog box?

View 2 Replies View Related

VBA To Open Print Window

Mar 23, 2012

This code takes all the data from a listbox and throws it into a sheet so that I can print it. I would like the print window to open ready to print this one sheet but allow the user to make sure the right network printer is selected before hitting print.

Code:
Private Sub cmdPrint_Click()
Dim i As Long
Dim ws As Worksheet
If Me.lstPrintRooms.ListCount = 0 Then
MsgBox "The are no rooms in the list to print"

[Code] .........

View 2 Replies View Related

Open, Print And Close

Apr 10, 2008

When i open an excel file, i need it to print 3 copies and then close itself without saving. This is because i have a file that must have 3 copies printed everyday. I know its lazy =) but i'm sure it will be very useful and i have seen it on a file before however i cannot successfully replicate it.

View 3 Replies View Related

Using Macro To Open Then Print A Webpage?

Dec 3, 2011

I am trying to use a macro in Excel to open a website (the URL is a named range in a worksheet) and then print the web page. I am able to open the webpage with the following code:

Sub View_Tech_Recalls()
Set ie = CreateObject("InternetExplorer.Application")
ie.navigate Range("Recall_URL")
ie.StatusBar = False
ie.Toolbar = True
ie.Visible = True
ie.Resizable = True
ie.AddressBar = False
End Sub

What do I need to add in order to print the open web page?

View 5 Replies View Related

Is There A VBA Way To Open Print Properties Dialog

Oct 19, 2007

I know I've seen where the print Dialog window can be opened.

Is there a way to open the Print PROPERTIES Dialog window?

View 9 Replies View Related

Open Print Window To Change Printer And Select Number Of Copies?

Oct 5, 2011

The below code opens up the printpreview window. This does not allow to select the correct (or change) printer it will automatic use the default printer.

Is there a way for the code to open the print window so I can change the printer and select number of copies?

Code:

Sub Print_Button()
Dim ws As Worksheet, cell As Range
Set ws = Sheets("main")
Set cell = Range("g2000").End(xlUp)
Do Until cell.Value ""
Set cell = cell.Offset(-1, 0)
Loop
ws.PageSetup.PrintArea = ("A2:" & cell.Address)
ws.PrintPreview
End Sub

View 3 Replies View Related

Refresh All Queries On Workbook Open When Finished Print To PDF Then Close Workbook

Jan 23, 2014

I have data that daily needs to be refreshed and printed to pdf.

I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.

I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).

View 4 Replies View Related

How To Put Formula Into Footer

Jun 18, 2004

Is there any way to put a formula into a footer OR have a footer read the contents of a particular cell on that page? The standard buttons for your footer does not allow this. How can I make this happen?

View 4 Replies View Related

Regarding Header And Footer...

Sep 18, 2009

Is there any way in excel 2007 to define a standard header and footer on the first sheet and use the same header and footer for all the remaining sheets within the workbook (same font and style as in first page).

Since I have around 25 sheets within the workbook i cannot do formatting and copy paste all the time. This will save my time alot.

View 2 Replies View Related

Put The Value In The Left Footer

Mar 25, 2009

I've been trying to figure out how to tell my left footer to automatically use the value of whatever "L2" is. How do I do this?

View 7 Replies View Related

Add Footer To A Workbook?

Jan 1, 2013

how to add footer with file path name to all worksheets of a workbook. I m adding one by one and my workbook has more then 110 sheets.

View 5 Replies View Related

Edit Footer

Jan 9, 2010

I am facing problem with my footer, which is as under:
___________________________________________
E-mail: xxx_yyyy@somewhere.com

My footer should be printed at the bottom of my paper as shown above. But, as expected, when I change margins of page or even change the printing preference, like best print to draft print or like that, it goes changing like this:
________________________________________________E
-mail:xxx_yyyy@somewhere.com
or like this:
_________________________________________________
__E-mail:xxx_yyyy@somewhere.com
So, it become headache for me. i have to first see the print preview and then after I can go for printing. How to deal with this problem?
There should be two lines in footer: first will have _____________________________________only and the second one will have E-mail:xxx_yyyy@somewhere.com

View 9 Replies View Related

Add Footer In All Workbooks

Mar 30, 2007

I want a footer to appear on every Excel workbook that I open. Including the following code in ThisWorkbook of each workbook works, but I want it to work on any workbook, i.e. by including the code in ThisWorkbook of my Personal Workbook (personal.xls)

Private Sub Workbook_Open()
Dim ws As Worksheet
Application. ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Changing footer in " & ws. Name
With ws.PageSetup
.LeftFooter = "&7Page &P of &N" & Chr(13) _
& "Path : " & ActiveWorkbook.FullName & Chr(13) _
& "Printed on &D at &T"
End With
Next ws
Set ws = Nothing
Application.StatusBar = False
End Sub

However, when I include the code in Personal.xls, I receive the following error message when I open an Excel file: Run-time Error '91': Object variable or With block not set

View 3 Replies View Related

Protect Header And Footer

Mar 2, 2007

Is there a way to protect the Header or the Footer from deletion? (or changes) I don't neccessary want to protect the whole sheet.

View 10 Replies View Related

Formatting Header Footer Via VBA

Nov 3, 2011

I have this fairly extensive code that puts a person's address in the footer when printed. It works fine except that if their zip code starts with a 0 it is dropped. I thought I could easily add some formatting code to fix it. I guess not. The code in red below is what I tried, but instead of fixing it, I now get a "False" in my footer.

Code:

With ActiveSheet
.PageSetup.CenterFooter = "&""Times New Roman,regular""&12 " _
& Format(Workbooks("Reports to Go....xlsm").Worksheets("Office Information").Range("d10").Text) _
& " - " & Format(Workbooks("Reports to Go....xlsm").Worksheets("Office Information").Range("d11").Text) _

[Code] ........

I should mention, the referenced cells are in a different workbook than the one this code is in.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved