password Protection

irresistible007

Board Regular
Joined
Nov 24, 2005
Messages
173
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:
Code:
Private Sub Workbook_Activate()

Sheets.Visible = False
logon.Show

End Sub

In Logon form:
Code:
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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?
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Like this:

Code:
    ActiveWindow.WindowState = xlMinimized
    UserForm1.Show

Will check out that article, could be useful.

Dom
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292

ADVERTISEMENT

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

Board Regular
Joined
Nov 24, 2005
Messages
173
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?
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
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
 

Forum statistics

Threads
1,136,269
Messages
5,674,743
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top