Password Enabling Printing VBA (Specific Sheet)

Rungravee Boonsud

New Member
Joined
Feb 19, 2019
Messages
11
I am looking to LOCK the printing activity in the excel UNLESS the user has a password and specific worksheet.

I am frustrated and need help. Please.

Thank you very much for kindly advise,
Rungravee.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,717
Office Version
365
Platform
Windows
must place this in ThisWorkbook module (NOT a standard module)
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If Not ActiveSheet.Name = "[COLOR=#ff0000]DataSheet???[/COLOR]" Then
        MsgBox "Cannot print this sheet"
        Cancel = True
    ElseIf Not InputBox("Pssword?") = "[COLOR=#ff0000]123???[/COLOR]" Then
        MsgBox "Wrong password"
        Cancel = True
    End If
End Sub
 

Rungravee Boonsud

New Member
Joined
Feb 19, 2019
Messages
11
many thank for advise

and May I have some problem

example

Sheet 1 = Can print No password

Sheet 2 = Cannot print

Sheet 3 = Can print but with password

Do you can advise this case ?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,717
Office Version
365
Platform
Windows
Adapt to suit your exact requirements

If more than one sheet for any Case then list each sheet name separated by comma
Sheet1, Sheet3, Sheet4 are not included inside Select Case below and therefore print normally

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Select Case ActiveSheet.Name
        Case "Sheet2"[COLOR=#ff0000],[/COLOR] "Sheet5"[COLOR=#ff0000],[/COLOR] "Sheet6"
                MsgBox "Cannot print this sheet"
                Cancel = True

        Case "Sheet3"
            If Not InputBox("Pssword?") = "123???" Then
                MsgBox "Wrong password"
                Cancel = True
            End If
    End Select
End Sub
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,717
Office Version
365
Platform
Windows
oops :oops:
Sheet1, Sheet3, Sheet4 are not included inside Select Case ...
should be ...

Sheet1
& Sheet4 are not included inside Select Case ...
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,717
Office Version
365
Platform
Windows
thanks for your feedback
(y)
 

Rungravee Boonsud

New Member
Joined
Feb 19, 2019
Messages
11
Yongle,

May I Have one question,

when typing a password for printing, Can we show hidden password (ex. xxxxxxx) ?

thank for your respond :)
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,717
Office Version
365
Platform
Windows
How about ...
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Const PW = "[I][COLOR=#ff0000]123???[/COLOR][/I]"
    Dim secret As String:   secret = String(Len(PW), "X")
    Dim msg As String:      msg = "Password?" & vbCr & vbCr & secret
    Select Case ActiveSheet.Name
        Case "Sheet2", "Sheet5", "Sheet6"
                MsgBox "Cannot print this sheet"
                Cancel = True

        Case "Sheet3"
            If Not InputBox(msg, , secret) = PW Then
                MsgBox "Wrong password"
                Cancel = True
            End If
    End Select
End Sub
 

Rungravee Boonsud

New Member
Joined
Feb 19, 2019
Messages
11
yongle,

cannot,

it's code show "XXXXXXXXX" before typing a password

but we need show "XXXXXXXXX" after typing a password (fill hidden password)
 

Forum statistics

Threads
1,082,587
Messages
5,366,486
Members
400,894
Latest member
frog9000

Some videos you may like

This Week's Hot Topics

Top