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.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Price</td><td style="font-weight: bold;text-align: center;;">Total</td><td style="font-weight: bold;text-align: center;;">Store</td><td style="font-weight: bold;text-align: center;;">Store</td><td style="font-weight: bold;text-align: center;;">Store</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;border-bottom: 1px solid black;;">Item</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;color: #FF0000;;">A</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Qty</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">A</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">B</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">C</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;border-top: 1px solid black;;">Apples</td><td style="text-align: right;border-top: 1px solid black;;"> $ 1.00 </td><td style="text-align: center;border-top: 1px solid black;;">35</td><td style="text-align: center;border-top: 1px solid black;;">20</td><td style="text-align: center;border-top: 1px solid black;;">10</td><td style="text-align: center;border-top: 1px solid black;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;;">Oranges</td><td style="text-align: right;;"> $ 1.50 </td><td style="text-align: center;;">15</td><td style="text-align: center;;">10</td><td style="text-align: center;;">5</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;;">Peaches</td><td style="text-align: right;;"> $ 2.25 </td><td style="text-align: center;;">35</td><td style="text-align: center;;">15</td><td style="text-align: center;;">10</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;border-bottom: 1px solid black;;">Limes</td><td style="text-align: right;border-bottom: 1px solid black;;"> $ 0.50 </td><td style="text-align: center;border-bottom: 1px solid black;;">20</td><td style="text-align: center;border-bottom: 1px solid black;;">5</td><td style="text-align: center;border-bottom: 1px solid black;;">5</td><td style="text-align: center;border-bottom: 1px solid black;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;border-top: 1px solid black;;">Total Qty</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;">105</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;">50</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;">30</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;">25</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;;">Total Price</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"> $ 146.25 </td><td style="font-weight: bold;text-align: right;;"> $ 71.25 </td><td style="font-weight: bold;text-align: right;;"> $ 42.50 </td><td style="font-weight: bold;text-align: right;;"> $ 32.50 </td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;color: #FF0000;;">Price A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;color: #92D050;;">Price B</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="font-weight: bold;;">Item</td><td style="font-weight: bold;;">Price</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Item</td><td style="font-weight: bold;;">Price</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Apples</td><td style="text-align: right;;"> $ 1.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Apples</td><td style="text-align: right;;"> $ 1.25 </td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Oranges</td><td style="text-align: right;;"> $ 1.50 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Oranges</td><td style="text-align: right;;"> $ 1.75 </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Peaches</td><td style="text-align: right;;"> $ 2.25 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Peaches</td><td style="text-align: right;;"> $ 2.00 </td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Limes</td><td style="text-align: right;;"> $ 0.50 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Limes</td><td style="text-align: right;;"> $ 0.25 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Totals</p><br /><br />



Thanks

Daren
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,516
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

deilert

New Member
Joined
Jul 20, 2007
Messages
9
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

New Member
Joined
Aug 5, 2014
Messages
23
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:

deilert

New Member
Joined
Jul 20, 2007
Messages
9

ADVERTISEMENT

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

New Member
Joined
Aug 5, 2014
Messages
23
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:

deilert

New Member
Joined
Jul 20, 2007
Messages
9

ADVERTISEMENT

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

New Member
Joined
Aug 5, 2014
Messages
23
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,516
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Hmmm, takes us back to having the data on 2 different sheets, which would be more stable
 

deilert

New Member
Joined
Jul 20, 2007
Messages
9
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:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Price</td><td style="font-weight: bold;text-align: center;;">Price</td><td style="font-weight: bold;text-align: center;;">Total</td><td style="font-weight: bold;text-align: center;;">Store</td><td style="font-weight: bold;text-align: center;;">Store</td><td style="font-weight: bold;text-align: center;;">Store</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;border-bottom: 1px solid black;;">Item</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;color: #FF0000;;">A</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;color: #FF0000;;">B</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Qty</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">A</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">B</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">C</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;border-top: 1px solid black;;">Apples</td><td style="text-align: right;border-top: 1px solid black;;"> $ 1.00 </td><td style="text-align: right;border-top: 1px solid black;;"> $ 1.25 </td><td style="text-align: center;border-top: 1px solid black;;">35</td><td style="text-align: center;border-top: 1px solid black;;">20</td><td style="text-align: center;border-top: 1px solid black;;">10</td><td style="text-align: center;border-top: 1px solid black;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;;">Oranges</td><td style="text-align: right;;"> $ 1.50 </td><td style="text-align: right;;"> $ 1.75 </td><td style="text-align: center;;">25</td><td style="text-align: center;;">10</td><td style="text-align: center;;">15</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;;">Peaches</td><td style="text-align: right;;"> $ 2.25 </td><td style="text-align: right;;"> $ 2.00 </td><td style="text-align: center;;">35</td><td style="text-align: center;;">15</td><td style="text-align: center;;">10</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;border-bottom: 1px solid black;;">Limes</td><td style="text-align: right;border-bottom: 1px solid black;;"> $ 1.00 </td><td style="text-align: right;border-bottom: 1px solid black;;"> $ 1.25 </td><td style="text-align: center;border-bottom: 1px solid black;;">20</td><td style="text-align: center;border-bottom: 1px solid black;;">5</td><td style="text-align: center;border-bottom: 1px solid black;;">5</td><td style="text-align: center;border-bottom: 1px solid black;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;border-top: 1px solid black;;">Total Qty</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;">115</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;">50</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;">40</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;">25</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;;">Total Price A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"> $ 171.25 </td><td style="font-weight: bold;text-align: right;;"> $ 73.75 </td><td style="font-weight: bold;text-align: right;;"> $ 60.00 </td><td style="font-weight: bold;text-align: right;;"> $ 37.50 </td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;;">Total Price B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"> $ 182.50 </td><td style="font-weight: bold;text-align: right;;"> $ 78.75 </td><td style="font-weight: bold;text-align: right;;"> $ 65.00 </td><td style="font-weight: bold;text-align: right;;"> $ 38.75 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Totals</p><br /><br />

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
 

Forum statistics

Threads
1,136,258
Messages
5,674,669
Members
419,520
Latest member
Jennifer4Dillon

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
Top