MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Ivan or some1.plz help ...(repost )


Posted by Jojo on January 05, 2002 4:03 AM

Thanks Ivan..but i think this method is not relevant..coz if the
user supplied the wrong password, all the data and
the formula will be deleted..so i have come with
1 solution..how to make user to key in a password
in order to print or to save the workbook..if the password
is wrong...the workbook wil close automatically and all
works will be not saved..thanks



Posted by Ivan F Moala on January 05, 2002 5:22 AM

In an empty module place this code

Option Explicit
Option Private Module

Function GetPwd() As Boolean
Dim Pwd As String

GetPwd = False
Pwd = InputBox("Enter password")
If Pwd = "test" Then GetPwd = True

End Function

In your ThisWorkbook object place this code;

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Not GetPwd Then
Cancel = True
ThisWorkbook.Close False
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not GetPwd Then
Cancel = True
ThisWorkbook.Close False
End If
End Sub


HTH


Ivan

Posted by Jojo on January 05, 2002 5:51 AM

Hi Ivan..it works fine at first..when i supplied
the right password..but when i try giving the
wronng password..illegal program shut down occured
says that excel make a violtion..why this thing
hapenned..could you fixed it Ivan..thanks and regards
for your time..FYI..i'm using office 2000


Posted by Tom Urtis on January 05, 2002 3:11 PM

Jojo,

Code can be added to solve that, but since it appears as if you are still in the thinking stage with this project, consider the trade-offs to your request:

(1) If a user knows the password and innocently enters it wrong, their work will be lost (not saved) and the file will close, which can be aggravating.

(2) If a user without a password has their auto save feature activated, this will also abruptly close the file.

(3) You would need to remember to keep the VB Project locked for viewing, with a separate password ideally, to keep code snoopers from learning your input box password. No big deal, just another thing to keep in mind.

(4) If you have the file in read only mode or haven't yet saved the file, the save as box will appear, so you'd need to add code for that possibility.

(5) I'm being eccentric with this one, but if someone really wants to print whatever data is otherwise covered up by the word "trial", they could print the screen to a Word document and then print that to see what's what.

Have you thought about a simpler approach, such as passcode protecting the file for authorized users only, or maybe just locking the cells and protecting the sheets.

For a more elaborate approach, if you only want to restrict users to a certain range, say B5:C10, not letting someone even select a forbidden cell, a worksheet event such as this might do it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("C5:D10")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Range("C5:D10").Select
'Optional, to avoid acceptable range to be entirely selected:
ActiveCell.Select
Application.EnableEvents = True
End If
End Sub

Or to select but not change,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Selection
If Intersect(cell, Range("C5:D10")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Application.Undo
Range("C5:D10").Select
'Optional, to avoid acceptable range to be entirely selected:
ActiveCell.Select
Exit For
End If
Next
Application.EnableEvents = True
End Sub

These do not protect against formatting changes or someone enabling events at start up, but they might get you closer to your objectives. Just passcode protection of worksheet cells might do the trick, or allowing non-passcode holding users to access only certain sheets. The choice is yours, good luck with whatever you decide.

Tom Urtis

Hi Ivan..it works fine at first..when i supplied