Password protecting different pricing for different users

[deilert]

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
	A	B	C	D	E	F
1		Price	Total	Store	Store	Store
2	Item	A	Qty	A	B	C
3	Apples	$ 1.00	35	20	10	5
4	Oranges	$ 1.50	15	10	5	0
5	Peaches	$ 2.25	35	15	10	10
6	Limes	$ 0.50	20	5	5	10
7	Total Qty		105	50	30	25
8	Total Price		$ 146.25	$ 71.25	$ 42.50	$ 32.50
9
10
11	Price A				Price B
12	Item	Price			Item	Price
13	Apples	$ 1.00			Apples	$ 1.25
14	Oranges	$ 1.50			Oranges	$ 1.75
15	Peaches	$ 2.25			Peaches	$ 2.00
16	Limes	$ 0.50			Limes	$ 0.25
Totals

Thanks

Daren

 

[Michael M]

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

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

 

[deilert]

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

 

[marcucciboy2]

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.

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

 

[deilert]

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.

 

[marcucciboy2]

Try it now

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

 

[deilert]

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?

 

[marcucciboy2]

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.

 

[Michael M]

Hmmm, takes us back to having the data on 2 different sheets, which would be more stable

 

[deilert]

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
	A	B	C	D	E	F	G
1		Price	Price	Total	Store	Store	Store
2	Item	A	B	Qty	A	B	C
3	Apples	$ 1.00	$ 1.25	35	20	10	5
4	Oranges	$ 1.50	$ 1.75	25	10	15	0
5	Peaches	$ 2.25	$ 2.00	35	15	10	10
6	Limes	$ 1.00	$ 1.25	20	5	5	10
7	Total Qty			115	50	40	25
8	Total Price A			$ 171.25	$ 73.75	$ 60.00	$ 37.50
9	Total 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!)

' 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