Results 1 to 9 of 9
  1. #1
    Kurt
    Guest

    Stop user to print out our file ==>

    I have create some workbook for my colleague..but they never
    appreciate it..they always take my works, put it in the diskette
    and bring it home.They don't care about my feeling and never
    thanks me. This is what I like you guys to help me. I don't
    want them to have hard copy of my files. So I want this :

    Whenever they try to print the pages( e.g I have 3 sheets in my workbook)
    a input box will appear and asking them for password. If they
    can't give the right password. a msg box will appear and
    said"you don't have authorized to keep this file as your hardcopy"
    And the printing process will be stop immediately.
    Is it possible. I'm thinking of something like
    private sub workbook_beforeprint..but I don't know how to do it..
    Wiil someone helps me on this. Thanks in advance

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stop user to print out our file ==>

    Kurt,

    You look like you may have found the right place, but I'll step through it anyway, just in case.

    Go into the VBA editor (Alt + F11). Select from the list on the left your worksheet. If it's got a "+" next to it, click on it. There's a line which says "Microsoft Excel Objects". If that's gor a "+" clcik on it. There' will now be a list underneat- SHeet1, Sheet2 (or whatever sheets you have in your workbook) and "ThisWorkbook". Double click on "ThisWorkbook".

    In the panel on the right, there's 2 dropdown boxes. the first says "(General)". Change that to "Workbook". From the second box, select "BeforePrint". You'll get something like this in the code window:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)

    End Sub


    Put some code in it like this:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim strText As String
    strText = InputBox("Enter the Password")
    If strText <> "Password" Then
    Cancel = True
    End If
    End Sub


    Before you give it to you friends, you will want to protect the code so that they can't see your code:

    Select Tools, VBA Project Properties, Protection- tick the box and enter your protection password.

    I hope that does the job.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    Kurt
    Guest

    Thanks.How do you applied it on certain sheets....

    I have 3 sheets.(sheet1,2 and 3)
    let say I want the user to print sheet1
    only but not sheet2 and 3..so how do I
    applied your code to do these..Thanks again..

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks.How do you applied it on certain sheets....

    Kurt,

    Maybe the easiest way to do this is to clear the print area for the sheets you don't want printed. For each sheet, "File, "Print Area", "Clear Print Area".

    Just in case they manually add the print area, put commands into the print routine which we've already written to clear the print area again:

    Sheets("Sheet2").PageSetup.PrintArea = ""
    Sheets("Sheet3").PageSetup.PrintArea = ""

    You won't even have to worry about passwords that way.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    Kurt
    Guest

    Re: Thanks.How do you applied it on certain sheets....

    Thanks again Whitfield..

    But I'm hoping You can set it sheet by sheet
    and I can attached the code to my preferered sheet..
    Can you do that..Thanks again..I'm hoping like
    these :

    Private Sub Worksheet_Activate()
    or
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    Is it possible..thanks

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks.How do you applied it on certain sheets....

    Kurt,

    The trouble is that if you print by selecting File, Print, you can then choose to print "All sheets" or just "Current sheet". Therefore, even if you can apply the code to a single sheet, it's possible for them to print all sheets.

    However, you can in the code decide which is the sheet they have open:

    if ActiveSheet.Name = "Sheet2" then ...

    Geoff
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Kurt
    Guest

    Re: Thanks.How do you applied it on certain sheets....

    Hi again Geoff,

    Thanks again.but where should
    I put the command line that you
    have just added..coz I'm a newby
    in this VBA pprogramming.Hope you
    can help.

    ps : Could you put write down
    the whole code again add include your new
    line comand..


    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim strText As String
    strText = InputBox("Enter the Password")
    If strText <> "Password" Then
    Cancel = True
    End If
    End Sub


    if ActiveSheet.Name = "Sheet2" then <==where i have to put this line ?.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks.How do you applied it on certain sheets....

    Kurt,

    You could try something like this:

    <pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ' Don't sllow sheets 2 & 3 to be printed
    Dim strText As String
    ' Set the Print area of sheets 2 & 3 to blanks-
    ' so that if the user chooses to print the workbook, these sheets will not print

    Sheets("Sheet2").PageSetup.PrintArea = ""
    Sheets("Sheet3").PageSetup.PrintArea = ""

    ' If the user has sheet 2 or sheet 3 open, ask for a password
    If ActiveSheet.Name = "Sheet2" Or ActiveSheet.Name = "Sheet3" Then
    strText = InputBox("Enter the Password")
    If strText <> "Password" Then
    Cancel = True
    End If
    End If
    End Sub
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    Kurt
    Guest

    Re: Thanks.How do you applied it on certain sheets....

    Thanks Geoff..You are genius..
    regards..hehe..maybe this is
    my day..bye

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •