Microsoft 2016 EXCEL document with VBA vs. M365

[MLC1980]

Hi,

I have an excel document with VBA that was created in Excel 2016. We have now moved to M365 and have come across an issue with the VBA.

The VBA is essentially to unprotect the sheet, reset all the fields and then re-protect the sheet. One of my users reported today that when they tried to copy and paste a formatted cell with a fill colour, it wasn't copying the fill colour. Google told me this was because 'Format Cell' wasn't ticked when protecting the document so I ticked it which fixed that issue, however, when the Reset VBA was used it unticked the 'Format Cells'. Any help on this would be much appreciated.

Sub ResetSPLPlanner()
'
' ResetSPLPlanner Macro
'
    ActiveSheet.Unprotect
    On Error Resume Next
    ' if it errors out all the cells are formulas - so do nothing ie do not clear contents
    Range("D13:D64").SpecialCells(xlCellTypeConstants, 23).ClearContents
    On Error GoTo 0
    Range("E13:E64").ClearContents
    
    With Range("D13:E64")
        .Cells.FormatConditions.AddUniqueValues              ' Need sheet protection removed for this to work
        With .Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With

    Range("H1:H2").ClearContents
    Range("L4:L5").ClearContents
    Range("M5").ClearContents
     'copy and paste formula for D13:D64
    Worksheets("VBA").Range("D13:D64").Copy
   Worksheets("Sheet1").Range("D13:D64").Select
   Selection.PasteSpecial
   Range("H7").Select
   ActiveCell.FormulaR1C1 = "Choose from dropdown"
  
   
    Range("A1").Select
    
    ActiveSheet.Protect UserInterFaceOnly:=True
End Sub

 

[MARK858]

Use the Macro recorder to record yourself setting the protection, you'll get something like the below to give you the syntax

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingRows:=True

You'll have to add the UserInterFaceOnly:=True

 

[MLC1980]

Use the Macro recorder to record yourself setting the protection, you'll get something like the below to give you the syntax

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingRows:=True

You'll have to add the UserInterFaceOnly:=True

@MARK858 thank you for your reply, I should have thought have doing that, I'm kicking myself. Works perfectly, thanks.

 

[MARK858]

You're welcome