?
Solved

Pass word protect named ranges

Posted on 2012-03-26
7
Medium Priority
?
282 Views
Last Modified: 2012-03-26
Hi,

I have a list of names and columns of data, i am going to name all these ranges

I would like some code that if you click anywhere in that named range you get prompted to enter a password that i can nominate

Has anyone got such code?

Thanks
Seamus
0
Comment
Question by:Seamus2626
  • 4
  • 2
7 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 37766279
This this code.  Paste it into the worksheet code pane for the worksheet containing the cells you want to protect.  Modify the code to match the range names in your worksheet.  I used names rngTemp1, rngTemp2, rngTemp3.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim PWord As String
PWord = "MyPassword"
If Not Application.Intersect(Range("rngTemp1"), Target) Is Nothing Then
    If Not PWordIsCorrect(PWord) Then Range("A1").Select
ElseIf Not Application.Intersect(Range("rngTemp2"), Target) Is Nothing Then
    If Not PWordIsCorrect(PWord) Then Range("A1").Select
ElseIf Not Application.Intersect(Range("rngTemp3"), Target) Is Nothing Then
    If Not PWordIsCorrect(PWord) Then Range("A1").Select
End If
End Sub

Function PWordIsCorrect(sPass As String) As Boolean
Dim s As String
s = InputBox("Enter the password", "Password required")
If s = sPass Then PWordIsCorrect = True Else PWordIsCorrect = False
End Function

Open in new window

Kyle
Q-27648596-RevA.xlsm
0
 
LVL 3

Expert Comment

by:Frank White
ID: 37766299
Small hint for using Kyle's solution:

You'll ideally want to protect the VBProject and the workbook itself with a password too, otherwise nothing's stopping the users from hitting ALT+F11 and looking at the code to find the right password.
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37766366
Here's an updated version.  I made the assumption that as soon as someone entered the correct password they should be allowed to keep working in the range until they clicked outside the boundaries.  At that point they will need to enter the password again to re-enter the range.  You will need to specify a cell into which the code can dump a variable.  In the example I used "I2".  Modify as necessary.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim PWord As String
PWord = "MyPassword"
If Not Application.Intersect(Range("rngTemp1"), Target) Is Nothing Then
    If Range("I2") <> "r1" Then
        If Not PWordIsCorrect(PWord) Then Range("A1").Select Else Range("I2") = "r1"
    End If
ElseIf Not Application.Intersect(Range("rngTemp2"), Target) Is Nothing Then
    If Range("I2") <> "r2" Then
        If Not PWordIsCorrect(PWord) Then Range("A1").Select Else Range("I2") = "r2"
    End If
ElseIf Not Application.Intersect(Range("rngTemp3"), Target) Is Nothing Then
    If Range("I2") <> "r3" Then
        If Not PWordIsCorrect(PWord) Then Range("A1").Select Else Range("I2") = "r3"
    End If
Else
    Range("I2") = "Nothing"
End If
End Sub

Function PWordIsCorrect(sPass As String) As Boolean
Dim s As String
s = InputBox("Enter the password", "Password required")
If s = sPass Then PWordIsCorrect = True Else PWordIsCorrect = False
End Function

Open in new window

Kyle
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Seamus2626
ID: 37766455
That looks really good Kyle, but i need each range to have a different password, not one password for the whole lot

Thanks
Seamus
0
 
LVL 12

Accepted Solution

by:
kgerb earned 2000 total points
ID: 37766486
Here you go.  Now you need to specify a unique password for each range.  Modify as necessary.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sPW1 As String, sPW2 As String, sPW3 As String
sPW1 = "PW1"
sPW2 = "PW2"
sPW3 = "PW3"
If Not Application.Intersect(Range("rngTemp1"), Target) Is Nothing Then
    If Range("I2") <> "r1" Then
        If Not PWordIsCorrect(sPW1) Then Range("A1").Select Else Range("I2") = "r1"
    End If
ElseIf Not Application.Intersect(Range("rngTemp2"), Target) Is Nothing Then
    If Range("I2") <> "r2" Then
        If Not PWordIsCorrect(sPW2) Then Range("A1").Select Else Range("I2") = "r2"
    End If
ElseIf Not Application.Intersect(Range("rngTemp3"), Target) Is Nothing Then
    If Range("I2") <> "r3" Then
        If Not PWordIsCorrect(sPW3) Then Range("A1").Select Else Range("I2") = "r3"
    End If
Else
    Range("I2") = "Nothing"
End If
End Sub

Function PWordIsCorrect(sPass As String) As Boolean
Dim s As String
s = InputBox("Enter the password", "Password required")
If s = sPass Then PWordIsCorrect = True Else PWordIsCorrect = False
End Function

Open in new window

Kyle
0
 

Author Closing Comment

by:Seamus2626
ID: 37766516
Thank you very much Kyle, thanks too  DaFranke, i will lock the project

Cheers,
Seamus
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37766552
You're welcome.  Glad to help.
Kyle
0

Featured Post

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!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

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