?
Solved

vba hidesheet with password userform

Posted on 2011-04-18
12
Medium Priority
?
421 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
0
Comment
Question by:mike637
  • 7
  • 4
11 Comments
 
LVL 30

Expert Comment

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

Sid
0
 

Author Comment

by:mike637
ID: 35426390
Hi SiddharthRout:

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

Michael
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35426402
Sorry Michael, that was a typo. I meant 'Recover'

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

Sid
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mike637
ID: 35426413
Yes sir,

That would be helpful!  Please assist.

Thank you.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35426422
Ok, May I modify the form to include Username as well for example

User Name - 10
Password - 5303

Sid
0
 

Author Comment

by:mike637
ID: 35426456

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

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35426467
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
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35426494
Is this what you want?

Sample Attached.

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
            Sheet2.Visible = True
            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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35426525
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
0
 

Author Closing Comment

by:mike637
ID: 35426548
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.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35426570
>>>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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question