Protecting sheet/workbook but allow formatting for a small range

[mrfahaji]

I am protecting my entire workbook (i.e. 5 sheets) with VBA. However, there is one section of one sheet that I would like users to be able to format (specifically highlight/fill).

For the sheet in question - 'Comps' - I have these lines of code in the ThisWorkbook module:

Sheets("Comps").Protect Password:="123", _
UserInterFaceOnly:=True, AllowFormattingCells:=True

But this allows formatting for the entire sheet. Using some code I saw elsewhere, I tried adding this - note the range I want to allow formatting for - but it didn't work.

Sheets("Computers").Range("CD4.DE26").Locked = False

Moreover, this code suggests to me that the cells are completely unlocked, which will allow deleting/typing etc. I ONLY want selecting & formatting as the options (it's a wordsearch, and I want the user to be able to highlight the letters).

Is there a way to do this?

Thanks!

 

[Jeffrey Mahoney]

A Couple Things. UserInterFaceOnly is to allow Macros to make changes to protected cells. It also allows grouping to function while the sheet is locked. This won't affect the user editing or formatting protected cells.

This doesn't look like the same sheet as "Comps": Sheets("Computers").Range("CD4.DE26").Locked = False

Any cells that you want the user to EDIT will need to be unlocked. The statement AllowFormattingCells:=True allows any cells to be formatted regardless of the Lock status.

When you say you want the user to Highlight the letters, do you mean you want to allow them to format certain characters in the cell? If so, then you will have to allow them to edit the cells.

 

[mrfahaji]

Apologies, I was meant to edit to say "Comps" instead of Computers.

I'll try and summarise the steps I have taken thus far:

1) Entire sheet is protected, but various cells on the sheet/workbook are unlocked, can be selected and typed in.

2) Formatting is usually disabled, but for this particular range (CD4:DE26) on this particular sheet ("Comps"), I want the user to be able to Highlight/Fill (the paint pot) the cell with a colour.

3) To achieve this pre-VBA, I would select 'allow formatting cells' when protecting the sheet. But I noticed that using the VBA code to protect the sheet loses this functionality, hence I added the AllowFormattingCells condition, which seems to have the desired effect.

4) BUT formatting (unlocked) cells is available for the entire sheet. Ideally I only want this to be an option for the range CD4:DE26.


I realise that having this function may necessarily mean that users can edit the cells - e.g. delete or change the letter - because I presume there is no way to ONLY allow formatting, but it's not in the user's interest to do that so I'm going to accept that's a slight weakness that has to exist.

Hopefully that makes it clearer. As for the Sheets("Comps").Range("CD4.DE26").Locked = False addition, I think that's probably just wrong, I saw it somewhere and tried to incorporate it, but not sure it's achieving what I want.

Thanks

 

[Jeffrey Mahoney]

4) BUT formatting (unlocked) cells is available for the entire sheet. Ideally I only want this to be an option for the range CD4:DE26.

Aahhh! You only want users to format certain cells and not others. I don't think that's possible through the normal Excel features. To make this happen with VBA would be elaborate. One would have to create a macro that would temporarily remove the sheet protection, allow the user to color the cell, and protect it again.

Or, if you had specific colors you wanted the user to choose from, like maybe up to 12, we could create a macro whereby the user selects the cells they want to color and they would click a button representing the color of choice and the macro would take care of the rest. Want help with that?

Jeff

 

[Jeffrey Mahoney]

This allows a user to double click any of the color cells below the button to toggle the color choice. The user then selects any number of cells in the allowed area and clicks the "Change Selected Cell Color" button.

The Color Choice are is called CBColorRng. The Allowed area is called AllowedRng. I protected the sheet using the UserInterfaceOnly so that the macro could change the cell colors without unprotecting the sheet all the time.

You can add any amount of color choices. You would have to alter the colors for the users and before protecting the sheet.

Private Sub ChangeCellColor_Btn_Click()
  Dim CBRng As Range
  Dim Cel As Range
  Dim CelClr As Long
  Dim Sht As Worksheet
  Dim AllowedRng As Range
  
  Set Sht = ActiveSheet
  Set AllowedRng = Intersect(Selection, Range("AllowedRng"))
  Set CBRng = Range("CBColorRng")
  
  If Not AllowedRng Is Nothing Then
    For Each Cel In CBRng
      If Cel.Value <> "" Then
        AllowedRng.Interior.Color = Cel.Interior.Color
        Exit For
      End If
    Next Cel
  End If
  
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  
  If Not Intersect(Target, Range("CBColorRng")) Is Nothing Then
    Cancel = True
    Application.EnableEvents = False
    Range("CBColorRng").Value = ""
    Target.Value = "P"
    Application.EnableEvents = True
  End If
  
End Sub




Sub ProtectSht()
  ActiveSheet.Protect UserInterFaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1234"
End Sub

 

[mrfahaji]

Thanks for this suggestion! It feels like this is the only possible solution so appreciate you going into that detail.

It's actually fine just for one colour (the user doesn't need to choose which, it could be a default colour, say, yellow).

 

[Jeffrey Mahoney]

Then all it needs is this:

Private Sub ChangeCellColor_Btn_Click()
  Dim AllowedRng As Range
  
  Set AllowedRng = Intersect(Selection, Range("AllowedRng"))
  
  If Not AllowedRng Is Nothing Then
    AllowedRng.Interior.Color = 65535                         'Yellow
  End If
  
End Sub