Locking out printing

Hi all,

Is it at all possible to lock out printing on a sheet until a particular
cell is entered/check box clicked.

I know you can remove certain buttons/toolbars but someone could still press
ctrl-p to print the sheet, I'm looking to lock it out entirely.

Many thanks for any help.

Colin.

-- 
Remove the 'old' to reply to me.


0
9/12/2005 4:37:40 PM
excel 39879 articles. 2 followers. Follow

5 Replies
363 Views

Similar Articles

[PageSpeed] 42

If the one cell was on a separate workbook....excel will not print an empty 
workbook.

"Col" wrote:

> Hi all,
> 
> Is it at all possible to lock out printing on a sheet until a particular
> cell is entered/check box clicked.
> 
> I know you can remove certain buttons/toolbars but someone could still press
> ctrl-p to print the sheet, I'm looking to lock it out entirely.
> 
> Many thanks for any help.
> 
> Colin.
> 
> -- 
> Remove the 'old' to reply to me.
> 
> 
> 
0
Mike1154 (1216)
9/12/2005 5:10:04 PM
How about stopping printing anything in that workbook until that cell is filled
in?

Put this in the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If IsEmpty(Me.Worksheets("Sheet1").Range("a1")) Then
        MsgBox "Please fill in Sheet1 Cell A1"
        Cancel = True
    End If
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about these kinds of events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm



Col wrote:
> 
> Hi all,
> 
> Is it at all possible to lock out printing on a sheet until a particular
> cell is entered/check box clicked.
> 
> I know you can remove certain buttons/toolbars but someone could still press
> ctrl-p to print the sheet, I'm looking to lock it out entirely.
> 
> Many thanks for any help.
> 
> Colin.
> 
> --
> Remove the 'old' to reply to me.

-- 

Dave Peterson
0
petersod (12004)
9/12/2005 5:27:54 PM
That's great, worked a treat.

My only question for now is, can you lock a specific sheet within a
workbook?

The code you gave me is fine but locks out all the workbook?

Thank you,

Colin.

-- 
Remove the 'old' to reply to me.



"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4325BA9A.F9D92C5B@verizonXSPAM.net...
> How about stopping printing anything in that workbook until that cell is
filled
> in?
>
> Put this in the ThisWorkbook module:
>
> Option Explicit
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>     If IsEmpty(Me.Worksheets("Sheet1").Range("a1")) Then
>         MsgBox "Please fill in Sheet1 Cell A1"
>         Cancel = True
>     End If
> End Sub
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> You can read more about these kinds of events at:
> Chip Pearson's site:
> http://www.cpearson.com/excel/events.htm
>
> David McRitchie's site:
> http://www.mvps.org/dmcritchie/excel/event.htm
>
>
>
> Col wrote:
> >
> > Hi all,
> >
> > Is it at all possible to lock out printing on a sheet until a particular
> > cell is entered/check box clicked.
> >
> > I know you can remove certain buttons/toolbars but someone could still
press
> > ctrl-p to print the sheet, I'm looking to lock it out entirely.
> >
> > Many thanks for any help.
> >
> > Colin.
> >
> > --
> > Remove the 'old' to reply to me.
>
> -- 
>
> Dave Peterson


0
9/13/2005 4:56:04 PM
I don't think so.

You can group sheets, then print multiple sheets.
You can do File|print|and chose entire workbook.

I don't know a way of getting "inside" those options to find out what sheets
you're printing.

This might work if you only print the activesheet:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If LCase(ActiveSheet.Name) = "sheet1" Then
        If IsEmpty(Me.Worksheets("Sheet1").Range("a1")) Then
            MsgBox "Please fill in Sheet1 Cell A1"
            Cancel = True
        End If
    End If
End Sub


But it's not too difficult to see how a user could "cheat" this.

=======
Another option is to put a cell in the print range (near the header).

=if(A1<>"","","This workbook is not complete--don't trust it!")

Or some warning that scares the heck out of anyone reading the paper copy.

Col wrote:
> 
> That's great, worked a treat.
> 
> My only question for now is, can you lock a specific sheet within a
> workbook?
> 
> The code you gave me is fine but locks out all the workbook?
> 
> Thank you,
> 
> Colin.
> 
> --
> Remove the 'old' to reply to me.
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:4325BA9A.F9D92C5B@verizonXSPAM.net...
> > How about stopping printing anything in that workbook until that cell is
> filled
> > in?
> >
> > Put this in the ThisWorkbook module:
> >
> > Option Explicit
> > Private Sub Workbook_BeforePrint(Cancel As Boolean)
> >     If IsEmpty(Me.Worksheets("Sheet1").Range("a1")) Then
> >         MsgBox "Please fill in Sheet1 Cell A1"
> >         Cancel = True
> >     End If
> > End Sub
> >
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > You can read more about these kinds of events at:
> > Chip Pearson's site:
> > http://www.cpearson.com/excel/events.htm
> >
> > David McRitchie's site:
> > http://www.mvps.org/dmcritchie/excel/event.htm
> >
> >
> >
> > Col wrote:
> > >
> > > Hi all,
> > >
> > > Is it at all possible to lock out printing on a sheet until a particular
> > > cell is entered/check box clicked.
> > >
> > > I know you can remove certain buttons/toolbars but someone could still
> press
> > > ctrl-p to print the sheet, I'm looking to lock it out entirely.
> > >
> > > Many thanks for any help.
> > >
> > > Colin.
> > >
> > > --
> > > Remove the 'old' to reply to me.
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12004)
9/13/2005 5:07:02 PM
Never mind, I can use the code you supplied on separate files, was just
being lazy trying to do it all in one workbook:)

Thanks again for the help - wonderful.

Colin.

-- 
Remove the 'old' to reply to me.


0
9/13/2005 6:00:57 PM
Reply:

Similar Artilces:

locking up
This might be more of a GDI question, but since I'm in MFC, I'll ask: Would BitBlt-ing to the screen repeatedly tend to lock up a program, after alt+tabbing to another application? If so, why? This seems to be happening to me, and I want to get to the bottom of the problem. What I have is either a fade loop or a screen-size redraw, both based on a timer and bitblt. Quite often, after I leave the program to go to another app, I cannot return and must "end task". "Steve Russell" <srussell@removethisinnernet.net> wrote in message news:%23YFL5Ks%23F...

Print even or odd pages ONLY
I have a 200 page publication that I have created in Mspub2003 and need to be able to print first only even pages, then odd pages on the back side of the first print. I can do this in Word but can't find the option in Pub. Assistance would be greatly appreciated. Ken Like I said before, it's very tempting to compare Word and Publisher but you can't depend on those two programs having the same features. I will assume that you have some sort of .pdf creating software on your system. (Most of us who use Publisher do. If you don't, you should.) Adobe Reader will allow you ...

Chaning Print Settings in Password Protected File
While the data is very good, the 'publisher' didn't put much thought and/or effort into what it looks like in printed form ... Is there a way to modify 'page setup' parameters for an Excel file that is password protected? Good evening Cheryl B Only by unprotecting the worksheet, changing the print settings and then reprotecting the sheet again. if you don't have the password, look here for how to do it without the password: http://www.mcgimpsey.com/excel/removepwords.html If you're unfamiliar with VBA and putting this code into a module I have an add-in av...

Losing some print options
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I've used Word for Mac since the day it was first released and dozens of different printers and never seen this problem. The same problem shows up using either a Canon or Kodak printer. <br> When printing a 20 to 50 page doc (double sided) I select to print odd pages first and it usually Q's up properly and prints normally. Then when I select to print the even pages, instead of a single Queue containing all of the even pages I get 2 or 3 Queues each containing part of the even pages. This makes us...

Publisher 2003 special size documents not printing properly in 200
We just upgraded to Publisher 2007. Our documents appear the same on the screen when opened but when in print preview they default to different size paper. We are using special size documents (4.26 X 5.75) when we notice this happening. They all default to 8 1/2 X 11. Is there anything we need to do to convert to 2007? Thank You for any help. A bit of a guess here: Open the file, select File, Print setup and input the paper size of choice. Be sure to do a Save to retain the settings. -- Don - Publisher 2000� Vancouver, USA "Ginger" <Ginger@discussions.microsoft.com...

Help printing an Invoice
Hi, it´s me again... In this ocation I have another question, how can I count and Control the quantity of items per sheet, cause, supposed that I have to do an Invoice where I sold 35 items of the same product, this mean that in my invoice appear 35 serials, and at the last of the invoice the items appear one on top of the other, that cause a bad impress on my clients, so how can I prevent this? how can I count the number of rows, so when there are 25, then add another sheet to the invoice... Please forgive my english, I don't if its clear, or not... Thanks again for your hel...

unwanted: Zoom adjust when adjusting Print Scale
Would someone be willing to review the file and advise. when in Print Preview - if I adjust the print scale - then the zoom ( screen view ) adjust and not the printed view. I have several co-workers which have had the same issue happen to them. It has to be a file setting - as other excel files do not do this. When I go to Page Break Layout and try to drag the blue dotted line - nothing happens, it will not move - or - it moves and gives me an error "Margins do not fit page size" and I will get blue dotted lines around all cells and 100's of pages. Have you change the page ...

How do I print up different labels in Publisher on the same page?
It can't figure out how to change the text for each label. In print preview it shows all the same text on every label and I need different text on each label...help please!! If you are using merge, the print preview is a bug in the program. Do a sample from print (print two rows as a test) -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Kittymayo1" <Kittymayo1@discussions.microsoft.com> wrote in message news:9C273930-65AD-46C2-856A-FCEDC31ACB4F@microsoft.com... > It can't figure out how to change the te...

Desktop Alert Locks Computer
Greetings, I am seeing a little quirk on some of my desktops. It only happens occasionally, but it happens to different models of PC. Not all the time, but once in awhile, I have users that complain of their PC freezing. The only remedy is to do a hard reboot. I have noticed in these instances, that the desktop mail alert ( the preview that pops up by the system clock when you get a new mail) is showing when it froze. If I go into Outlook and turn off the desktop alert, then the PC freezing stops. So, I know that the Desktop Alert is the cause of the problem. Has anyone else ever seen this? Th...

re:Printing HTML Messages in Outlook 2000
Still no joy. Problem is similar but I cant print any HTML mail messages in Outlook whereas I can print web pages in Explorer. Its definitely something to do with printing HTML messages as I have no problem printing plain text messages. Have removed header footers in Outlook page set up but I cant find any reference to encapsulated printing in the properties box of my Epson Stylus Color 880. Any further clues ? ...

Lock formula to cell
I want a formula to lock to a cell so that when I add or delete a column the formula will always remain with say column H I know if the sheet is Password protected this will prevent this happening but that is no use when I have to unprotect the sheet to delete a column. Anyone got thoughts on this? regards Pat Hi Pat without using VBA you can't achieve this Frank Pat wrote: > I want a formula to lock to a cell so that when I add or delete a > column the formula will always remain with say column H > > I know if the sheet is Password protected this will prevent this >...

locked excel File ??
Hi . I am working with my colleges with one Excel file in a shared file system. Sometime - when I open the file, there is the message "locked from College" - but he is definetly out of office and shut down his PC. We are using Excel 2003 + Excel 2007. What can I do?? THnks for help Susan ...

Locked out of database
Not sure what happened but get a message saying "database has been converted from a prior version of Access by using DAO compactdatabase and is now in a partially converted state." It says I should be able to import tables into a new data base but when I try this it just gives the same message about converting and the data base just doesn't open. Please help because lots at stake, have to do a report based on these tables so am waiting with anticipation!!!x Hi Pete, Usually this means you are opening an earlier version of Access in a new version. Make a copy of the data...

Outllook2007 to open my Outlook2002 Win7 says files locked.
I'm following 2002 to 2007 instructions step by step. When I point Outlook to my ..pst file, I get access restriction messages. How do I make sure these files can be accessed by Outlook 2007? Thanks. Which exact steps are these? From your Outlook 2002 machine, copy the pst-file to your Outlook 2007 machine and place it in a convenient location such as My Documents\Outlook\ In Outlook 2007 you can then reconnect to the pst-file via; File-> Open-> Outlook Data File... For details see; http://www.howto-outlook.com/howto/backupandrestore.htm -- Robert Sparnaai...

Printing problem in Excell 2007
One of my suppliers sends me his price list in an excel file, this file has a large Logo picture on the first page. When I look at the spreadsheet in any of the Excel views it looks great however when I print it or send it to Adobe Acrobat for PDF'ing the logo almost doubles in size covering data on the spreadsheet. I have noticed that some other of my spreadsheets do the same thing. Does anyone have any idea why excel does this? ...

excel locks up after selecting a cell #2
excel locks up after selecting a cell. When ever, I select a Cell, that will automatically selects all the cell and this freezes the entire computer. Can any body who would help me resolve this issue? Please help.... ...

How to print 4 page design on 4.5" x 5.5" top fold cards
I have designed a 4 sheet (front, middle 1, middle 2, and back) as an invitation. This is set up as a 4.25"x 5.5" top fold card. How do I set up Publisher and HP 7280 All-in-one printer to print on card stock. I expect to use two side printing, but Publisher does not have print set up for these standard size cards. You will need 4.25" x 11" card stock OR use 8.5" x 11" stock which you plan to cut to size after printing the invitations. I'm going to assume you will be using 8.5" x11" stock. This will create two invitations per sheet of...

How do I print a text file ?
Hi, I am new to the group and wonder if any of you could offer any advice. I am trying to print a text file directly from my visual c++ application to the default printer without displaying the print dialog What is the best way of doing this ? If it is not possible to print directly from a text file, what commands to I use in between CDC::StartPage () To print the following Hello, my name is Joe Bloggs How are you I hope you can help with this problem etc..... CDC::EndPage () Thanks in advance Paul Paul, More info on printing than you can comfortably shake a stick at: http://www....

Access reports and forms have gray background and print as gray
My Microsoft Access reports and forms have gray background and print as gray. I had Office 2002 XP small business edition as well as Pro. i uninstalled both and installed Office 2003 no SP as of now. But i am still having the same issue. the reports look gray not white. the forms do not have a color. when i try changing the back color property, the color of the form still remains gray. One more thing is that when i try the create report/from wizards, i see shadows on the Next, Cancel buttons. This is a new computer so i dont know if anything is wrong with the video card. I dont have any othe...

Print preview?
How can I see a preview of how my page will look when printed before printing it? Using Excel 2008. Thanks! One Option: Click the Page Layout View button in the lower left corner of the window. You can also go to File> Print, then click the Preview button to preview the document in Apple's Preview app. -- HTH |:>) Bob Jones [MVP] Office:Mac "Rick" <someone@yahoo.com> wrote in message news:eDuOY66%23KHA.348@TK2MSFTNGP06.phx.gbl... > How can I see a preview of how my page will look when printed before > printing it? Using Excel 2008. ...

how do I remove my name from the top of the page when printing
I don't want my name to print on the top of the page when I print out my contacts. I want to pass out the contact information to my business agents but my name appears at teh top of every page. How can I get rid of my name "Engineers98" <Engineers98@discussions.microsoft.com> wrote in message news:BF9AFF8D-222E-4903-B430-C16BCED3ED44@microsoft.com... > I don't want my name to print on the top of the page when I print out my > contacts. I want to pass out the contact information to my business > agents > but my name appears at teh top of eve...

Money "lock-out" solution-thanks Kurt
Kurt Thanks for the idea. It worked. I unistalled money 2002 and the reinstalled using custom mode...up and running again! Thank -you What happens to all your data when you do that? Do you still have your data? I tried reinstalling, but no luck. I hate to uninstall, since I would lose all the data, right? Would appreciate info. Thanks, Alice >-----Original Message----- >Kurt > >Thanks for the idea. It worked. I unistalled money 2002 >and the reinstalled using custom mode...up and running >again! Thank -you >. > Your data is in a file *.mny. This is ind...

Printing orientation
I am looking to print different orientations on the same excel sheet does anyone know if this is possible or do they all have to be the same? Hi, when you select the range, go to print and when the dialog box shows up click on Preview, Page setup, choose the orientation you need, then click OK, Print "Jennifer" wrote: > I am looking to print different orientations on the same excel sheet does > anyone know if this is possible or do they all have to be the same? note, you can do the above for specific ranges on the sheet, but when you print, you cannot have ...

Lock autoshape to fixed position
Hello, Does anybody know if it is possible to keep an autoshape to a fixed position. I have a worksheet that has two panes, one is frozen i.e. a fixed header and scrollable rows. On the bottom of the page I want to put a button bar made of shape buttons. The problem is that when I scroll, the buttons scroll too! It would be nices if I can keep them in position. I know that is is possible to add a second window, but that does not look so well... Any ideas? Multiple fixed panes would be a great option! Change the alignment option to be absolute. Picture properties > Layout tab > Advan...

Formats for Printing Macros
Excel 2007 I have been asked to produce some printouts of macros. How do I set the print format (margins, etc.)? -- Gary''s Student - gsnu200909 Do you mean you want to print the code itself out? If so, I don't know the specific answer to the question as you asked it; but, if this was for me to do, what I would do is just copy/paste the macros into a Word document, format them there as needed and then print them out from there. -- Rick (MVP - Excel) "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message news:...