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

 

[marcucciboy2]

Sorry I didn't reply yesterday, but here ya go. So you'll have to update the range in HideData() to include every cell you used, and if you want to manually unprotect the sheet the password would be: secret.

' Hides all pricing information
Sub HideData()


'Unlock all (update range as necessary)
    Worksheets("Totals").Unprotect Password = "secret"
    Worksheets("Totals").Range("A1:Z99").Locked = False
    
'Lock and hide A
    Worksheets("PriceA").Visible = False
    
    Worksheets("Totals").Columns("B").EntireColumn.Hidden = True
    Worksheets("Totals").Rows("8").EntireRow.Hidden = True
    
    Worksheets("Totals").Columns("B").Locked = True
    Worksheets("Totals").Rows("8").Locked = True
    
'Lock and hide B
    Worksheets("PriceB").Visible = False
    
    Worksheets("Totals").Rows("9").EntireRow.Hidden = True
    Worksheets("Totals").Columns("C").EntireColumn.Hidden = True
    
    Worksheets("Totals").Rows("9").Locked = True
    Worksheets("Totals").Columns("C").Locked = True
    
'Protect Sheet
    Worksheets("Totals").Protect Password = "secret"

End Sub


' Shows selective pricing sheets, columns, and rows based upon password
Sub ShowData()


'Group specific password
    otherpass = InputBox("Please Enter A Password To Show Pricing")
    
'Unlock and unhide A
    If otherpass = "aPassword" Then
        
        Worksheets("Totals").Unprotect Password = "secret"
        
        Worksheets("PriceA").Visible = True
    
        Worksheets("Totals").Columns("B").Locked = False
        Worksheets("Totals").Rows("8").Locked = False
        
        Worksheets("Totals").Columns("B").EntireColumn.Hidden = False
        Worksheets("Totals").Rows("8").EntireRow.Hidden = False
        
        Worksheets("Totals").Protect Password = "secret"
    
'Unlock and unhide B
    ElseIf otherpass = "bPassword" Then
    
        Worksheets("Totals").Unprotect Password = "secret"
        
        Worksheets("PriceB").Visible = True
    
        Worksheets("Totals").Rows("9").Locked = False
        Worksheets("Totals").Columns("C").Locked = False
        
        Worksheets("Totals").Rows("9").EntireRow.Hidden = False
        Worksheets("Totals").Columns("C").EntireColumn.Hidden = False
            
        Worksheets("Totals").Protect Password = "secret"


    Else
    
        MsgBox ("Not a valid password")
        
    End If
    
End Sub