Protecting Sheet (1 password) | Protecting Cells Within Same Sheet (2nd Password)

[MixedUpExcel]

Hi,

I'm not sure if this can be done - having looked at various examples on sites / forums - nothing I've tried seems to combine together.

I'm not looking for a VBA Solution as it's hard for me to control who will have access to the workbook and educate them on what 'macro enabled or not enabled' means.

Scenario
I have built an order tracker with various drop downs / formulas / conditional formatting etc.
I have locked all cells.
I have selected cells that need user entry and unlocked these.
I have protected the sheet with only the 'Select unlocked cells' option ticked

This works as expected.

I have been asked by a different department if they can be the only ones who can enter data / select drop down box etc in columns L and M, whilst allowing everyone else to continue to enter data into the 'unprotected' cells.

For Columns L and M, I select the cells / columns and then in the Ribbon - Review > Allow Edit Ranges > New (provide Title / Range is already selected / add Range Password)

Then I protect the sheet. Options ticked are Select Locked Cells and Select Unlocked Cells - leaving password blank for testing purposes.

Outcome
1. I can select cells that were unlocked and enter data
2. I can select cells that were locked BUT if I try to enter data I get a message 'The cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet'
3. I can select a cell in Column L and when I try to enter data, it asks me for a password.

Points 1 and 3 above are as I want them to be.
Point 2 is not. I want those cells to not enable the users to select them (therefore no pop-up message is required as they can't select the cell).

As far as I can tell, I have all the parts to this puzzle. I'm just possibly not putting them in the right order / selecting the right tick box?

Can anyone advise which is the correct way to do this so that my outcome of Points 1 and 3 are correct and users can not select the locked cells as per Point 2?

Thanks in advance.

Simon

 

[Skyybot]

Uncheck Allow to Select Locked cells.

 

[MixedUpExcel]

Uncheck Allow to Select Locked cells.

Hi Skybot,

When I Uncheck Allow to Select Locked Cells, I then can't select the cells that are protected by a different password.

If I change the cells which are protected by a different password to be unlocked cells, then anyone can enter data into those.

So unfortunately, that doesn't appear to work.

Thanks.

Simon

 

[Skyybot]

Point 2 is not. I want those cells to not enable the users to select them (therefore no pop-up message is required as they can't select the cell).

I apologize. I thought that is what you were asking for.

 

[MixedUpExcel]

I apologize. I thought that is what you were asking for.

Hi.

You're kinda right.

Just to put it all in one (it was a bit mixed in my first post).

I need these 3 conditions to be met.

1. I can select cells that were unlocked and enter data.
2. I can not select cells that were locked (it will not allow me to click into those cells)
3. I can select a cell in Column L or Column M and when I try to enter data, it asks me for a password.

Points 1 and 2 are the usual Format > Unlock selected Cells - then protect the sheet with only the 'Select unlocked cells'

My issue is part 3. I don't know what to do to make that work, alongside Points 1 and 2

Thanks.

Simon

 

[Skyybot]

I think that I found a way to get close to the results that you are looking for but it requires a bit of VBA code. Use your current Password setup and paste the following code into Sheet1 of your Project.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 5 Or Target.Column = 6 Then '<---------this will have to be changed to suit your needs. It only doesn't allow user to select E and F columns
    Application.DisplayAlerts = False
    Range("A1").Select
    Application.DisplayAlerts = True
Else
    Application.DisplayAlerts = True
End If
End Sub

 

[Skyybot]

I think that I found a way to get close to the results that you are looking for but it requires a bit of VBA code. Use your current Password setup and paste the following code into Sheet1 of your Project.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 5 Or Target.Column = 6 Then '<---------this will have to be changed to suit your needs. It only doesn't allow user to select E and F columns
    Application.DisplayAlerts = False
    Range("A1").Select
    Application.DisplayAlerts = True
Else
    Application.DisplayAlerts = True
End If
End Sub

@MixedUpExcel , don't use this. I need to tweek it.

 

[Skyybot]

Ok. I think I have it. Keep your working Protection (Allow Edit Range password and Sheet protection password should be different). All cells formatted as Locked except the ones anyone can edit. Paste this code in Sheet1. You will have to unlock sheet to edit any column past M.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Me.ProtectScenarios
    Case "True"
        If Target.Column > 13 Then
            Me.Range("A1").Select
        End If
    End Select

End Sub