How do I detect whether a cell has protected formatting in Excel VBA?-CodePudding

I have some VBA code that I would like to set the format (forecolour) of all cells in a given workbook that match some criteria (essentially this is to auto-mark use of a particular UDF). If the user has protected sheets in their workbook, they may have (sensibly) protected them in such a way that formatting is still permitted. How do I check (from the VBA Range object representing the cell) whether a cell on any given worksheet is good to make format edits to?

I am aware the route-one answer to this will be an error handler to try it and handle the cases that fail - but as this has to run on every cell in the UsedRange of every sheet, I want it to be fast. I also realise that this is VBA, so there may not be a faster or more elegant way - but there is a lot of collected wisdom on here, hence my asking!

CodePudding user response：

You need to firstly check if the sheet is protected and do what you need if not.

If Protected, you should check only the range you try changing if is locked, has cells locked on is not and do the job only if is unlocked. You cannot check if the cells have a protected format... The next code will show you (I think) what is to be done in such a case:

Sub testSheetProtectedLockedCells()
   Dim sh As Worksheet, rng As Range
      Set sh = ActiveSheet: Set rng = sh.Range("A2:C4")

       'Just for testing: _________________________________________
       rng.Locked = Not rng.Locked 'lock - unlock the range...
       rng.cells(1, 1).Locked = Not rng.cells(1, 1).Locked ' lock-unlock one cell of the range
       '___________________________________________________________

       If Not sh.ProtectionMode Then
            DoIt rng
       Else
            If rng.Locked = False Then
                DoIt rng
            ElseIf IsNull(rng.Locked) Then
                MsgBox "Cell(s) of the range """ & rng.address & """ are locked." & vbCrLf & _
                       "Please, unlock all the range and run the code again!", vbInformation, _
                       "Locked cells in the range to be processed..."
            Else
                MsgBox "The range """ & rng.address & """ is locked." & vbCrLf & _
                        "Please, unlock it and run the code again!", vbInformation, _
                        "Locked range to be processed..."
            End If
       End If
End Sub

Sub DoIt(rng As Range) 'do here the job you need...
    Debug.Print rng.address, rng.Locked
End Sub

CodePudding user response：

I think error handling is still the way to go. But as far as I can tell, if formatting fails for one cell in your sheet, it will fail for all other cells, even if those cell are unlocked.

Try the following strategy: The idea is that if formatting fails for any cell, you stop attempting to format the current sheet and move on to the next.

Sub MyProcedure()
  Dim sht As Worksheet
  Dim cl As Range
  
  For Each sht In ThisWorkbook.Sheets
    
    For Each cl In sht.UsedRange
    
      On Error Resume Next
      
      ' Format the cell in a DIFFERENT procedure so that
      ' if an error occurs the rest of formatting lines are
      ' are not attempted (this is the key idea)
      ApplyFormat cl
      
      If Err.Description = "Application-defined or object-defined error" Then
        Err.Clear
        Exit For
      End If
                              
    Next cl
    
    '* Either reset your error handling here if you have more code for each sheet
    On Error GoTo 0
    
    ' ...more code
  
  Next sht

  '* Or eset you error handling here
  On Error GoTo 0
  
  ' ...more code
  
End Sub

Sub ApplyFormat(cl As Range)
  ' apply your formatting here
End Sub