Allowing only select users to print a worksheet from a workbook

[yamalady]

I am challenged with finding a way to only allow certain users of a workbook to be able to print a particular worksheet. I have searched online and tried a few different codes, but nothing seems to be working. I am new to codes, so I need easy.

[Fotis1991]

In "This Workbook" module, try this code.

Please Login or Register  to view this content.

[yamalady]

Thank you Fotis! But I am getting a compile error: Variable not defined. It is highlighting "entrypw". Any ideas?

Code error.jpg

[berlan]

Maybe even trigger a msgbox in the event of wrong password,

Please Login or Register  to view this content.

Just notice that enableevents must be "True" for it to work.

Please Login or Register  to view this content.

[berlan]

For Fotis code, you get an error since you need to declare all variables using "Option Explicit". For that, add the line (in the beginning of the procedure):

Please Login or Register  to view this content.

This type of inputbox (member of VBA.Interaction) is treated as a string.

[yamalady]

Thanks Berlan! This worked for the whole workbook, how do I get this to work for just one of the Worksheets and not all the Worksheets?

[Fotis1991]

Thanks Berlan!


yamalady

Use the code to that worksheet module.

[berlan]

Fotis, to my knowledge it's not a worksheet specific event, but I may be wrong

yamalady, since you can print multiple sheets at a time, I tried to cater fort that. Maybe:

Please Login or Register  to view this content.

See if this works.

[yamalady]

I tried both yours Fotis, and yours Berlan, in the specific Worksheet module I am needing it in, but now neither are working at all. It just prints with no warnings or even allowing to enter a password. I know it's got to be something simple I am overlooking. I even tried changing the word "workbook" to "worksheet"...but that did not work either.

I tried this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = True
Const sPW As String = "12345"
If InputBox("Please enter the correct password!", "Password Required") <> sPW Then
Cancel = True
MsgBox "Wrong password, unable to print.", vbCritical
End If
End Sub

Then I tried this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim entrypw as string
Const pw = "12345"
entrypw = InputBox("Please enter the correct password!", "Password Required")
If entrypw <> pw Then
Cancel = True
Else
Exit Sub
End If

End Sub

Then I tried it without the "Dim":

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Const pw = "12345"
entrypw = InputBox("Please enter the correct password!", "Password Required")
If entrypw <> pw Then
Cancel = True
Else
Exit Sub
End If

End Sub

[yamalady]

Berlan, that did not work either.

[berlan]

Did you put my code in the Thisworkbook module and not in a Sheet module?

Then change Sheet1 to the sheet you want to protect...

[yamalady]

Berlan, yes, I moved your code to the Sheet module and yes, I did change the sheet name to the proper name.

[berlan]

You may have misunderstood me, put it in the Thisworkbook module, and see how it works for you.

The code won't be triggered in a Sheet module.

[yamalady]

Sorry, I did misunderstand. Ok, I moved it back to ThisWorkbook. It did work, but the interesting part is that it now prints doubles of the other sheets that are ok to print.

[Fotis1991]

Fotis, to my knowledge it's not a worksheet specific event, but I may be wrong

Very kind of you!!



Ok. so let's do something that i know better than codes!

yamalady

Your post#9, does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)