password Protection

[irresistible007]

I am upto a project in Excel which will keep track of our company's expenses...

the file will only be accessible by pre-defined users, to access, the user will have to choose his username from a combo box and then will have to type the password (pre-defined too...) in a text box below, clicking OK will verify the info and will let him open the file... this whole thingy will be on a VB Form (logon Form)...

I want excel to hide all the sheets in background and only show the logon form at first, the sheets must only be visible upon successfull verification of user but close the workbook if failed...

prob is that excel comes up with the error saying " A workbook must contain atleast 1 visible worksheet... i have coded as below:

In thisworkbook:

Private Sub Workbook_Activate()

Sheets.Visible = False
logon.Show

End Sub

In Logon form:

Private Sub UserForm_Activate()
ComboBox1.Clear
ComboBox1.AddItem "User1"
ComboBox1.AddItem "User2"
End Sub

Private Sub cmd_ok_Click()
Dim myuser As String
myuser = ComboBox1.Text
Dim mypassword As String
mypassword = TextBox1.Text
If myuser = "User1" And mypassword = "Pass1" Then
    logon.Hide
ElseIf myuser = "User2" And mypassword = "Pass2" Then
    logon.Hide
Else: Workbooks.Close
End If
End Sub

 

[Domski]

How about minimizing the workbook as it opens and then maximizing it if the correct password is entered.

Aren't you going to run into problems if the user chooses not to enable macros when they open the workbook though?

 

[irresistible007]

How to do that by the way

Disabling macros is another thingy... solution here

 

[Domski]

Like this:

    ActiveWindow.WindowState = xlMinimized
    UserForm1.Show

Will check out that article, could be useful.

Dom

 

[Domski]

That's a neat trick.

One way to bypass it though if the user happened to know the name of the hidden sheet I think they will still be able to reference cells in it by adding a new worksheet and inserting formula like this:

='Hidden sheet name'!A1

They'd probably had to have access at some point to gain that knowledge so I'm probably just being a bit picky (or sneaky).

Dom

 

[irresistible007]

by the way another issue arised here... when i clicked close button on the userform... it innocently closed...Lolx.. me so stupid... whats the way to deal wid this one?

 

[Domski]

Never tried but have a look here:

http://www.mrexcel.com/board2/viewtopic.php?t=99046

Dom

 

[DominicB]

Good morning irresistible007

I have a template available that will open with all sheets except for one blank one hidden. A userform prompts for a username and password and once entered correctly the sheets that the particular person is allowed to see are unhidden (using the xlVeryHidden instruction). The user rights are assigned by the administrator who can see all sheets and settings. The workbook is quite configurable and contains full instructions to get you started.

I can send this to anyone requesting it via e-mail.

HTH

DominicB
dominic@dom-and-lis.co.uk