Cancel Printing Except Q

[Sean]

I have the following code which prevents priniting of a document. Would
it be possible to allow printing if the correct password was entered
within a userform? So on clicking the Print Icon an input box which
required the password would appear, if it is correct, document prints,
if it isn't document doesn't. If so how would I do it?

Thanks

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

 

[Dave Peterson]

You could add some code to disable events (including the _beforeprint event) in
your code that gets/validates the password.

dim myPwd as string
'some validation here
if mypwd = "oktoprint" then
application.enableevents = false
worksheets("whatever").printout
application.enableevents = true
end if

And the _BeforePrint routine won't even run.

 

[Sean]

Thanks Dave.

How aboutthe attached code, I picked and tweaked from this NG, seems to
work okay. Can I chnage the type of Msg Box

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sReply As String
sReply = Application.InputBox( _
Prompt:="Please enter the password", _
Title:="Password Required", _
Type:=2)
If sReply = "1234" Then
Cancel = False
'''Run the password protected code.
MsgBox " Click OK to commence Printing"
Else
Cancel = True
'''Do not run the password protected code.
MsgBox "Sorry, incorrect Password. You are not permitted to Print this
Document"
End If
End Sub

 

[Dave Peterson]

There are options to show different buttons on the msgbox. But I'm not sure
that's your question.

If it is, VBA's help will explain what you can use.

 

[Sean]

Slight twist in the code. If a the user is listed in MyUser2 range name
then, print otherwise don't.

My Problem is that it's not printing regardless if the user is listed.
Anything wrong with the code?



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myArray As Variant
Dim arName As String
arName = "MyUsers2"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value
With Application
If IsError(.Application.Match(.UserName, myArray, 0)) Then
Cancel = True
Else
Cancel = False
End If
End With
End Sub

 

[Dave Peterson]

My bet is the usernames that you put in that don't match what excel has for the
..username.

Maybe a few:
msgbox application.username
will help you see the differences.

Remember that the user can change this name pretty easily.
Tools|Options|general and a little typing and they can print (or mess it up so
that they can't print!).

Slight twist in the code. If a the user is listed in MyUser2 range name
then, print otherwise don't.

My Problem is that it's not printing regardless if the user is listed.
Anything wrong with the code?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myArray As Variant
Dim arName As String
arName = "MyUsers2"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value
With Application
If IsError(.Application.Match(.UserName, myArray, 0)) Then
Cancel = True
Else
Cancel = False
End If
End With
End Sub

 

[Sean]

Dave, get this one. My Username was correct (so it should have printed)


So then I looked at my Range Name (which was only referenced to one
cell i.e. A1), but when I expanded that Range to A2 - it printed and
didn't print when the user was not permitted

Quirky or what?

 

[Dave Peterson]

Application.match() expects an array for that second argument. If it's not an
array, you get an error--whether or not the name matched the (only) entry or
not.

One way around it:

Option Explicit
Sub testme()
Dim myArr As Variant
Dim res As Variant
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("test1")

myArr = myRng.Value
If myRng.Cells.Count = 1 Then
'do a simple test
If StrComp(Application.UserName, myArr, vbTextCompare) = 0 Then
MsgBox "print it"
Else
MsgBox "Don't print it"
End If
Else
res = Application.Match(Application.UserName, myArr, 0)
If IsNumeric(res) Then
MsgBox "Print it"
Else
MsgBox "Don't print it"
End If
End If

End Sub

Another way is to make sure that myArr is an array.

Option Explicit
Sub testme()
Dim myArr As Variant
Dim res As Variant
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("test1")

myArr = myRng.Value
If myRng.Cells.Count = 1 Then
myArr = Array(myRng.Value)
Else
myArr = myRng.Value
End If

res = Application.Match(Application.UserName, myArr, 0)
If IsNumeric(res) Then
MsgBox "Print it"
Else
MsgBox "Don't print it"
End If

End Sub

That second version is a little slicker, huh?

Dave, get this one. My Username was correct (so it should have printed)

So then I looked at my Range Name (which was only referenced to one
cell i.e. A1), but when I expanded that Range to A2 - it printed and
didn't print when the user was not permitted

Quirky or what?

 

[Sean]

Thanks Dave, I got around it as in my real data I had more than one
value. Guess my test should be the same as real