We help IT Professionals succeed at work.

vba hidesheet with password userform

mike637
mike637 asked
on
Medium Priority
467 Views
Last Modified: 2012-05-11
Hello Experts:

I am at a crossroads in writing some code for a workbook.  I want to keep as much of this in code since the information in the hidden worksheet includes salary amounts. There will quite a few salaries listed so I thought it was best to have them on a hidden worksheet.

I want to create a userform that verifies a user password to unhide the sheet.

I don't know if I should put a vlookup on the hidden sheet or if there is a way to do the same thing in code.  Again - I want to keep as much of this hidden as possible.

On sheet1 there will be a button to click - which opens the UserForm to enter the password. Range("F4")  on sheet1 will be the user number that is keyed.

Here is my lookup range - the first set of numbers are the user numbers and the second set are the passwords. There are a total of 12 folks on the distribution list with user numbers.

10 = 5303
11 = 7712
12 = 8022
30 = 2415
36 = 5609
39 = 2902
40 = 2209
41 = 2424
42 = 0298
53 = 8703
54 = 8798
55 = 3160

The other part of this is depending on how the code is written - I need assistance in how to check the value of the textbox.text to the entered password.

Any guidance or suggestions would be appreciated.  I started the process and have attached the basic format.

Thanks,
mike637



Book1.xls
Comment
Watch Question

Yes, it can be done but storing passwords in Excel Sheet is very risky. It's easy to reccoer them.

Sid

Author

Commented:
Hi SiddharthRout:

I am sorry for my ignorance - what is "reccoer" and what does this entail?

Michael
Sorry Michael, that was a typo. I meant 'Recover'

If you still want to make it, I can help you.

Sid

Author

Commented:
Yes sir,

That would be helpful!  Please assist.

Thank you.
Ok, May I modify the form to include Username as well for example

User Name - 10
Password - 5303

Sid

Author

Commented:

Please feel free to adjust or modify as needed.  I am the obedient follower. :)

15-20 mins and you will have your worksheet :)

Also this is an Excel question. This should have been in Excel Zone :) Probably you would have gotten an answer earlier :)

Sid
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Here is another version where the workbook is protected so the user manually cannot unhide the sheets.

Sample attached. The password that I have used to protect the workbook is "mike"

Sid

Code Used

Option Explicit

Dim UName As String, Pass As String

Sub CommandButton2_Click()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim aCell As Range
    Dim lastRow As Long
    
    If Len(Trim(TextBox1.Text)) = 0 Then
        MsgBox "Please Enter your username"
        Exit Sub
    End If
    
    If Len(Trim(TextBox2.Text)) = 0 Then
        MsgBox "Please Enter your password"
        Exit Sub
    End If
    
    UName = Trim(TextBox1.Text)
    Pass = Trim(TextBox2.Text)
    
    Set ws1 = ActiveSheet
    Set ws2 = Sheets("Password")
     
    lastRow = ws2.Range("A" & Rows.Count).End(xlUp).Row
 
    Set aCell = ws2.Range("A1:A" & lastRow).Find(What:=UName, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        If aCell.Offset(, 1).Value = Pass Then
            ActiveWorkbook.Unprotect "mike"
            Sheet2.Visible = True
            ActiveWorkbook.Protect "mike"
            Unload Me
        Else
            MsgBox "Incorrect UserName or Password"
            TextBox1.Text = ""
            TextBox2.Text = ""
        End If
    End If
End Sub

Sub CommandButton1_Click()
    Unload Me
End Sub

Open in new window

Login-Example.xls

Author

Commented:
This works for me!  It is great - Thank you.

I take it is would take some work for someone to get to the password sheet if I lock VB?

I want to keep as much protected and hard for others to get in to the hidden sheets.
>>>I take it is would take some work for someone to get to the password sheet if I lock VB?

Well not exactly :)

If you know 'how' then you can retrieve the password easily :)

Sid
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.