Password protecting different pricing for different users

deilert

New Member
Joined
Jul 20, 2007
Messages
9
Folks,

I have a spreadsheet with lots of parts and quantities that I'd like to discuss with two different parties (A and B), however I have two different pricing with both parties. I'd like to be able to chat with both of them at the same time to work toward the right quantities, but I'd rather not reveal pricing (keep the columns and rows hidden.) I would like both parties to be able to independently "enable" their respective pricing to be shown on the spreadsheet through a password. This way I don't have to continue to maintain multiple versions of the file. Both parties are respectful of not sharing the pricing with each other.

I've created a very simplified version I what I'm discussing below. I have the two pricing lookups in different tabs however.


Excel 2010
ABCDEF
1PriceTotalStoreStoreStore
2ItemAQtyABC
3Apples$ 1.003520105
4Oranges$ 1.50151050
5Peaches$ 2.2535151010
6Limes$ 0.50205510
7Total Qty105503025
8Total Price$ 146.25$ 71.25$ 42.50$ 32.50
9
10
11Price APrice B
12ItemPriceItemPrice
13Apples$ 1.00Apples$ 1.25
14Oranges$ 1.50Oranges$ 1.75
15Peaches$ 2.25Peaches$ 2.00
16Limes$ 0.50Limes$ 0.25
Totals




Thanks

Daren
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Maybe this....but a couple of words of warning
1. passwords are easily broken in Excel, so security is weak
2. I'd also password protect the VBE as well, so they can't see the password code.
3. the 2nd code will hide both sheets

Code:
Sub ShowSheet()
    Const pword1 = "Password"
    Const pword2 = "otherPassword"
    On Error GoTo errhandler
    pw = InputBox("Please Enter A Password To Unhide Sheet")
    If pw = pword1 Then Worksheets("Sheet1").Visible = True
    If pw = pword2 Then Worksheets("Sheet2").Visible = True
errhandler:
End Sub


Sub HideSheet()
    Worksheets("Sheet1").Visible = xlVeryHidden
    Worksheets("Sheet2").Visible = xlVeryHidden
End Sub
 
Upvote 0
Michael,

Thanks. This just hides worksheets correct? Is there a way to hide the row / column with password protection? Column B and row 8 in the above sheet.

Thanks,

Daren
 
Upvote 0
I'm not exactly sure who is not allowed to see what, but this example hides row 8 from one group and column B from the other.


Code:
Sub HideData()
    Worksheets("Sheet1").Rows("8").EntireRow.Visible = False
    Worksheets("Sheet1").Columns("B").EntireColumn.Visible = False
End Sub

Sub ShowData()
    Const pword1 = "Password"
    Const pword2 = "otherPassword"
    On Error GoTo errhandler
    pw = InputBox("Please Enter A Password To Unhide Sheet")
    If pw = pword1 Then 
    Worksheets("Sheet1").Rows("8").EntireRow.Visible = True
    If pw = pword2 Then 
    Worksheets("Sheet1").Columns("B").EntireColumn.Visible = True
errhandler:
End Sub
 
Last edited:
Upvote 0
Thanks, however I'm getting a runtime error '438' when I run HideData(). Object doesn't support this property or method.

Maybe a version issue?

I'm running MS Excel 2010 ver. 14.0.6106.5005 (32 bit) under Win 7.
 
Upvote 0
Try it now

Code:
Sub HideData()
    Worksheets("Sheet1").Rows("8").EntireRow.Hidden= True
    Worksheets("Sheet1").Columns("B").EntireColumn.Hidden= True
End Sub

Sub ShowData()
    Const pword1 = "Password"
    Const pword2 = "otherPassword"
    On Error GoTo errhandler
    pw = InputBox("Please Enter A Password To Unhide Sheet")
    If pw = pword1 Then
    Worksheets("Sheet1").Rows("8").EntireRow.Hidden = False
    ElseIf pw = pword2 Then
    Worksheets("Sheet1").Columns("B").EntireColumn.Hidden = False
    Else
    MsgBox "Not a valid password."
    End If
errhandler:
End Sub
 
Last edited:
Upvote 0
Oh, this is working beautifully now. One last challenge however.

The way to "protect" the sheet from folks seeing the pricing vs. just un-hiding the columns would also prevent folks from changing the quantities? Looking back to my original sheet above, we'll be changing the quantities together without pricing shown, then each party would go off and check out pricing for the new changes.

Thoughts?
 
Upvote 0
Sorry, I'm not really sure what you're asking for. Even if cells are hidden their formulas will update, and if someone Were to simply unhide the columns and rows on their own, they won't need the password to change the values. Really we're just distracting them from the fact that they could just unhide things on their own. If, however, you want us to password protect the rows and columns in addition to (or instead of), that can happen. Just describe what you want a little more in depth.
 
Upvote 0
Hmmm, takes us back to having the data on 2 different sheets, which would be more stable
 
Upvote 0
If, however, you want us to password protect the rows and columns in addition to (or instead of), that can happen. Just describe what you want a little more in depth.

Yes hide and password protect (if possible.) Here's an updated sheet with three tabs, Totals, PriceA, PriceB. The PriceA tabs would just be lookup tables. Here's the Totals tab:

Excel 2010
ABCDEFG
1PricePriceTotalStoreStoreStore
2ItemABQtyABC
3Apples$ 1.00$ 1.253520105
4Oranges$ 1.50$ 1.752510150
5Peaches$ 2.25$ 2.0035151010
6Limes$ 1.00$ 1.25205510
7Total Qty115504025
8Total Price A$ 171.25$ 73.75$ 60.00$ 37.50
9Total Price B$ 182.50$ 78.75$ 65.00$ 38.75
Totals


Ideally I'd like to hide columns B and C, and rows 8 and 9. The only way party A could see their row/column combination (B/8) they would enter a password. The same for party B (C/9.)

Here's the code I have thus far from you guys (which is quite sweet!)

Code:
' Hides all pricing information
Sub HideData()
' Hide A
    Worksheets("PriceA").Visible = xlVeryHidden
    Worksheets("Totals").Rows("8").EntireRow.Hidden = True
    Worksheets("Totals").Columns("B").EntireColumn.Hidden = True
' Hide B
    Worksheets("PriceB").Visible = xlVeryHidden
    Worksheets("Totals").Rows("9").EntireRow.Hidden = True
    Worksheets("Totals").Columns("C").EntireColumn.Hidden = True
End Sub
' Shows selective pricing sheets, columns, and rows based upon password
Sub ShowData()
    Const apword = "aPassword"
    Const bpword = "bPassword"
    On Error GoTo errhandler
    pw = InputBox("Please Enter A Password To Show Pricing")
    If pw = apword Then
        Worksheets("PriceA").Visible = True
        Worksheets("Totals").Rows("8").EntireRow.Hidden = False
        Worksheets("Totals").Columns("B").EntireColumn.Hidden = False
    ElseIf pw = bpword Then
        Worksheets("PriceB").Visible = True
        Worksheets("Totals").Rows("9").EntireRow.Hidden = False
        Worksheets("Totals").Columns("C").EntireColumn.Hidden = False
    End If
errhandler:
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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
Back
Top