Unprotect sheet when printing

Have the below code to print a sheet, increment a invoice number and remove 
protection from sheet, at the moment the passowrd is nothing I nee to change 
this to qwert and edit the formula to up protect sheet on print and re 
protect it after the incrementing of the invoice number. Any help would be 
appreaciated.

Sub dayprint()
'
' dayprint Macro
' 
'

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
     Sheet13.Unprotect
      'Increment the invoice number
      [e3] = [e3] + 1
      Sheet13.Protect
      
' Save Macro
' Macro recorded 19/08/2005 
'
'
    ActiveWorkbook.Save
End Sub
0
bbc1 (29)
8/30/2005 11:41:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
430 Views

Similar Articles

[PageSpeed] 29

Hi BBC1,

Try something like:

Sub dayprint()
    Const PWORD As String = "qwert"

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    With ThisWorkbook.Sheets("Sheet13")
        .Unprotect Password:=PWORD
        'Increment the invoice number
        With .Range("E3")
            .Value = .Value + 1
        End With
        .Protect Password:=PWORD
    End With

    ActiveWorkbook.Save
End Sub


---
Regards,
Norman



"bbc1" <bbc1@discussions.microsoft.com> wrote in message 
news:33268ABD-B4B8-45DD-A8E0-F9D403456F89@microsoft.com...
> Have the below code to print a sheet, increment a invoice number and 
> remove
> protection from sheet, at the moment the passowrd is nothing I nee to 
> change
> this to qwert and edit the formula to up protect sheet on print and re
> protect it after the incrementing of the invoice number. Any help would be
> appreaciated.
>
> Sub dayprint()
> '
> ' dayprint Macro
> '
> '
>
>    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
>     Sheet13.Unprotect
>      'Increment the invoice number
>      [e3] = [e3] + 1
>      Sheet13.Protect
>
> ' Save Macro
> ' Macro recorded 19/08/2005
> '
> '
>    ActiveWorkbook.Save
> End Sub 


0
normanjones (1047)
8/31/2005 12:15:01 AM
Thankyou works great.

"Norman Jones" wrote:

> Hi BBC1,
> 
> Try something like:
> 
> Sub dayprint()
>     Const PWORD As String = "qwert"
> 
>     ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
>     With ThisWorkbook.Sheets("Sheet13")
>         .Unprotect Password:=PWORD
>         'Increment the invoice number
>         With .Range("E3")
>             .Value = .Value + 1
>         End With
>         .Protect Password:=PWORD
>     End With
> 
>     ActiveWorkbook.Save
> End Sub
> 
> 
> ---
> Regards,
> Norman
> 
> 
> 
> "bbc1" <bbc1@discussions.microsoft.com> wrote in message 
> news:33268ABD-B4B8-45DD-A8E0-F9D403456F89@microsoft.com...
> > Have the below code to print a sheet, increment a invoice number and 
> > remove
> > protection from sheet, at the moment the passowrd is nothing I nee to 
> > change
> > this to qwert and edit the formula to up protect sheet on print and re
> > protect it after the incrementing of the invoice number. Any help would be
> > appreaciated.
> >
> > Sub dayprint()
> > '
> > ' dayprint Macro
> > '
> > '
> >
> >    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
> >     Sheet13.Unprotect
> >      'Increment the invoice number
> >      [e3] = [e3] + 1
> >      Sheet13.Protect
> >
> > ' Save Macro
> > ' Macro recorded 19/08/2005
> > '
> > '
> >    ActiveWorkbook.Save
> > End Sub 
> 
> 
> 
0
bbc1 (29)
8/31/2005 12:41:31 AM
Reply:

Similar Artilces:

how do i move an image on a sheet
I Have an image on a sheet which i want to have moving from left to right while the sheet is being viewed when cell B4 has "Please wait" in it. ...

Printing Sales document
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C9D490.3508AD30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello People, here is another issue. a.. I have created a User and assigned her a role of "Order Processor" = which is canned in GP 10.0=20 b.. For this Role ID, I am assigning a Task ID called "Print Sales = Documents" under Sales and I specifically checked all boxes related to = printing sales documents.=20 c.. I have granted the "access to print the Sales documents&qu...

Unprotect Worksheet
Well, I got myself into a jam ... working with a nine sheet workbook and for some stupid reason I password protected one of the sheets and can't remember it. Any ideas? TIA. The silence on this one is deafening ... You can hear a pin drop. I finally got the pwd straight and am now back in business. Lesson learned: No more pwd's when protecting unless they are going somewhere where you have no control.. On 1/31/2010 4:08 PM, Jethro Pull wrote: > Well, I got myself into a jam ... working with a nine sheet workbook > and for some stupid reason I password pro...

How do i Print on thick paper in microsoft publisher??
Im trying to print on a pice of card from Microsoft Publisher 07. I have done it before, but was using 2003 version. There used to be a thick1, thick2, thick3 option where you could click on it to let the printer now the paper was thicker then normal. Just wondering if the option is right there in front of me or it really has dissapeared! Help! Paper thickness is a function of your printer. Check with the manufacturer's web site to see if your printer driver is current. If your printer setup has a paper preference it would be per publication. Look at File, Print Setup, Printer Detai...

Formatting unprotected sections
Hi, I'm currently working on a template (.dotx) for Word 2007 where I want to protected several sections with the 'Filling in forms' protection. So far it's ok - but in the unprotected section I'm unable to use things like 'Bullets' and 'Numbering' - why is this, and mere importantly how do I solve this? Any help would be greatly appreciated /JEN As you are using Word 2007, consider using Content Controls rather than formfields. Alternatively, use a UserForm (Custom Dialog) to get the user's input See http://gregmaxey.mvps.org...

Typing and printing notes in Powerpoint 2007
I want to print a series of notes along with the slide. PPT 2007 only allows one slide per page using 'Notes' and the type is very small. This wastes a lot of paper with only one slide per page as much of the page remains blank. Is there another methodolgy for printing more slides (ie. 3 slides) with notes on each page? -- TD - Northville Hi Tim, as you say - the notes page is only available with one per page. however, if when you print you set the printer properties to print multiple slides to a page you will overcome this problem. (under print/ printer properties...

Macro to define a print area
Does anyone know of a macro that when ran would check row by row and when a row was found that was blank it would set the print area for the row above it and on up. I have useres that may have 300 rows or may just have 10 rows. I would like to be able to have them click on a button, (macro), and the macro would search for the first blank row and set the print margin to that. I need this because right now if the person with 300 rows goes to print its fine but if the person with 10 rows prints it ends up printing 150 blank pages. Thank you Hi JRoberts If you clear you printarea Excel will onl...

Printing Group Members?
Is there a way to print the members of a Distribution Group in Active Dir? Dave, Check script below (it's a quite old script, but should work not too bad ;)) --cut-here-- ' ' showDL.vbs ' ' Usage: showDL.vbs <mail-enabled DG nickname> ' ' Const C_sIndent = " " Function findObject(ByVal queryText) On Error Goto 0 Set rootDSE = GetObject("LDAP://RootDSE") Set oCon = CreateObject("ADODB.Connection") Set oCmd = CreateObject("ADODB.Command") oCon.Provider = "ADsDSOObject" oCon.Open "ADProvider&quo...

Unprotecting what Excel says is unprotected ???
Good evening all the peeps of the Excel Community. This one has me completely flammuxed but hopefully someone can spot my obvious error that I can't ... I have a workbook with 26 sheets. At some time, I know I password protected it ... somehow. Each time I open my workbook, it asks me "xxx.xls' is reserved by Joe Bloggs. Enter password for write access, or open read only." I have been through each and every one of the 26 sheets using Tools - Protection to unprotect but none are protected, nor are any Workbook protected. My only guess is that at some stage,...

Multiple sheets
Hi I have several workbooks with a sheet in for each day (W1Mo etc) which all link to a daily totals sheet in a seperate workbook. What I've done is named the total figure cell in each column and then linked that to the daily sheet, but I have approx 120 named cells per workbook. Is there an easier/better way? Thanks Wendy Why are you using named cells? This will take longer to set up than normal cell references. Pete On Jan 25, 10:01=A0am, "Wendy" <we...@sunnygirl.com> wrote: > Hi > > I have several workbooks with a sheet in for each day (W1Mo etc)...

printing merged booklets
Have a 16 page booklet that I want to add mailing details to front cover. Merge to document sucessfuly but cannot seem to get it just to ptint 16 page booklets it triues to treat the whole merged file as one document. In word I can specifiy the no of pages to make up the booklet. Would welcome help. ...

Flagged info
I use the completed box in the flag along with the description when I'm closing an issue that has been tracked throughout via email. In the past, in other places I've worked, the date and time will also be printed, not just the flag and flag status. I noticed in one other contract that the defaults somehow were set so that date/time didn't appear and in this current contract, same thing. This current job, though, is all about tracking BFs, though, and so I cannot afford not to have the date/time the flag info was completed printed out. Does anyone know how to change it so that ...

Taking a cell from sheet 2 to sheet 1 (newbie here)
Hey Folks, I am very new here and just joined the forums. I wanted to as how to take information from a cell on sheet #2 to sheet #1. There ar going to be many instances with the workbook I am making of doing this. I just have yet to figure out how to make this happen. Also if i helps at all the �digit� that will be in the initial cell (being copie from) is going to be from a function of some sort ( ex: D4 x D8 ) Thanks for any and all answers. If this sounds confusing just ask i the thread. Also if I happened to post this in the wrong part sorr and bare with as I am new. Thank You ...

Print Titles greyed out in print preview
Hi. I can go underneath page setup and use the Print Titles option (under sheet) fine. When I go into print preview, then go to setup, it is greyed out all of a sudden. Seems to happen with any file, one sheet or multiple. Using Excel 2K. Anyone have an explantion of why this is happening? Thanks Print Titles and Print Area cannot be changed while in Print Preview mode. That's the way Excel works. HTH, Gary Brown >-----Original Message----- >Hi. I can go underneath page setup and use the Print >Titles option (under sheet) fine. When I go into print >preview, then go ...

excel vba
I have set up my sheet to protect using a password name login which the runs the macros. It is set up so that the sheet's cells cannot b edited, but the person can copy/paste the info onto another sheet. This is a problem because there are hidden cells which contai sensitive data. Any ideas -- Message posted from http://www.ExcelForum.com Hi, just a comment that i just found out, check out this link that I was refered too. That password that you setup doesn't do squat when you have access to this... http://www.straxx.com/excel/password.html I was mad when I saw this, when mean...

Filter on sheet with some cells protected?
Hi all, I have a spreadsheet which has four columns of formulas protected so people can't change them! However, i still want people to be able to filter the spreadsheet and this isn't working, is there a way of locking those cells but not stopping people from filtering? Kirstie If you are using Excel 2000 or earlier, then you do not have the option to allow filtering when you protect a sheet. However, one way around this is to copy/move your formulae to another sheet which you can protect (and hide if you want to), and then have links in your first sheet to the cells with the...

Printing problem
This may also relate to scrolling. An Edit control under Win9x/Me can only hold 65535 characters of text, whereas one under W2K/XP can hold over 2 billion. That has caused me a problem. I've had users to try to drop more text into my Edit control (in an EditView) than will fit in it. I worked around that by checking the length of the incoming text against the buffer, removing lines at the top of the buffer if needed to provide space, then putting the incoming text into the control at the bottom. That worked, but I lose the text that is taken out of the buffer. The problem I have is that ...

Print Macro
Cell D5 represents a variable number from 1-120, the input changes the data accordingly on the whole workbook. I have input a macro to enter the number 1, print the document range A1:AB54 and then input number 2 and so on until i have 120 pages. This a frequently changing schedule and for review and distibution, would rather have a pdf doc. I changed the printer from the default to a pdf writer, when the macro runs it prints to the pdf doc but when the pdf document opens up, the macro stops after the first one, it does not go back to excel to work on number 2 etc.. Is there a ...

4 postcards on 1 sheet
publisher 2007 If I go to print and click multiple copies it will print 4 on a page but then I have to print one page at a time. I tried the page set up/ postcard/ us letter and couldn't get that to work. publisher 2003 was alot easier! -- linda Use the Avery US Letter postcard setup. 3263 works every time. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "linda" <linda@discussions.microsoft.com> wrote in message news:55AD7066-6417-454A-8222-DAFED428F979@microsoft.com... > publisher 2007 If I go to print an...

print 1 paper from 2 sheets
How can i print the range a1:m10 from sheet 1 AND a11:r10 from sheet 2 on ONE piece of paper ? Anybody knows a solution ? ---------------------------------------- Mijn Postvak In wordt beschermd door SPAMfighter 203 spam-mails zijn er tot op heden geblokkeerd. Download de gratis www.SPAMfighter.com vandaag nog! Create a new sheet. Copy A1:M10 of Sheet1 to that new sheet Copy A11:R10 of sheet 2 to that new sheet Print from there. If you have to do this lots of times, you can select A1:M10 edit|copy to the new sheet shift-edit|paste picture link And the same with A11:R10. By using paste ...

Formatting In Unprotected Section of Template
Good morning, In Office 2003 you were able to add bullets in an unprotected section of a template - now, with 2007, this option is no longer available. Any other suggestions? - I have used the Insert Symbol and Alt 0149 but is there another way? Thank you, Jeanne In an unprotected section of a protected document, you can press Ctrl+Shift+L to quickly apply the List Bullet style to the selected paragraphs. So-called quick styles can be applied via the Styles box on the Home tab. Also note that you can apply any style via the Style box which you can add to the Quick Acc...

Hyperlink to specific excel sheet from powerpoint or Word
Can we create a hyperlink to a particular sheet (or a defined range) in a excel document from powerpoint / word?? ~Yogendra Yogendra Yes. Insert > Hyperlink and type something like this C:\Dick\NG\02 Feb\Book1.xls#Sheet1!A10 Everything after the # is called the subaddress and determines where in the sheet the link goes. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com yogendra.joshi@gmail.com wrote: > Can we create a hyperlink to a particular sheet (or a defined range) > in a excel document from powerpoint / word?? > > ~Yogendra ...

populating sheet with data from other sheets
Email: gbeard12@sbcglobal.net TOTALS: 38 Sets in Progress 16,764 7,908 8,856 47.2% Priority Year Set Info Set Total Have Need % Complete Updated LOW 1952 TOPPS 407 0 407 0.0% LOW 1953 TOPPS 274 0 274 0.0% LOW 1954 TOPPS 250 0 250 0.0% LOW 1955 TOPPS 206 0 206 0.0% LOW 1956 TOPPS 340 0 340 0.0% LOW 1957 TOPPS 407 1 406 0.2% LOW 1958 TOPPS 494 2 492 0.4% LOW 1959 TOPPS 572 33 539 5.8% LOW 1960 TOPPS 572 10 562 1.7% SCROLL DOWN! LOW 1961 TOPPS 587 3 584 0.5% LOW 1962 TOPPS 598 0 598 0.0% LOW 1...

Watermark Image on Sheet
Is it possible to place a 'Watermark' image on a worksheet i.e. Company logo as you can do in Word? Thanks Hi John, In order to print a watermark. Watermark, printing of, Robert Rosenberg. Also Ron de Bruin has a zip file. http://google.com/groups?selm=%23Af%244ZZeAHA.1692%40tkmsftngp02 http://www.rondebruin.nl/files/Watermark.zip --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "John" <johndoconnor@yaho...

Printing email #2
I'm using Outlook 2003 and still can't find a way to print just the first page of an email. Am I missing something? I searched past messages and all I learned is that people have been begging for this capability for a long time--makes me wonder whether the people who design this software actually use it. ...