All formatting when Locking VBA Worksheet

[PebbleDash]

I have a worksheet where I have managed to use VBA code to be able to select multiple items from a drop down list. I have also added extra code to allow for this to work when the worksheet is password protected. However, when the worksheet is password protected, I can't then use Autofilter, amend the height of any of the rows and I can't insert any new rows. As the worksheet needs to be password protected, is there anything I can do regarding the formatting of the cells or do I need to remove the VBA code for the multiple selections from a drop down list?

 

[DanteAmor]

Hi and welcome to MrExcel

When you protect the sheet, you can mark Format cells to formatting of the cells, mark Format rows to amend the height of any of the rows, mark Use Autofilter to use Autofilter.
That way the sheet will continue to be password protected, but it will allow you some actions on the sheet.

 

[PebbleDash]

I did try this but once you enter something into one of the 'unprotected' cells and then try to either enter something into another cell or use the autofilter, the spreadsheet says everything is protected. When you then 'unlock' the worksheet and then lock it, the only 2 items that are selected are 'Selected Locked Cell' and 'Select Unlocked Cells'. The spreadsheet has unticked the options that I had originally selected of allowing you to format rows, cell, insert rows or use the autofilter.

 

[myall_blues]

When you protect the sheet with code you need to specify the options you want selected (as TRUE) in the code.

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

In the case of filtering, the filters have to be 'in-place' before you protect the sheet. You can set the protection to allow you to use the auto-filtering but you can't turn it on or off once the sheet is protected.

 

[DanteAmor]

When you then 'unlock' the worksheet and then lock it

In your code, in the instruction when you protect the sheet again, you must mark True those that you want to remain and False those that you want to turn off:
Check the following example. Change "abc" to your password:

  ActiveSheet.Unprotect Password:="abc"
'
  'your code
  'your code
  'your code
'
  ActiveSheet.Protect Password:="abc", _
    DrawingObjects:=False, _
    Contents:=True , _
    Scenarios:=False, _
    AllowFormattingCells:=True , _
    AllowFormattingColumns:=True , _
    AllowFormattingRows:=True , _
    AllowInsertingColumns:=False, _
    AllowInsertingRows:=True , _
    AllowInsertingHyperlinks:=False, _
    AllowDeletingColumns:=False, _
    AllowDeletingRows:=False, _
    AllowSorting:=TrTrue ue, _
    AllowFiltering:=True , _
    AllowUsingPivotTables:=True