Merge/Unmerge Cells in a Protected Sheet

Yazzay

New Member
Joined
Jul 22, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,

I have an excel sheet where I have some cells protected, unfortunately I can't merge/unmerge cells while the sheet is protected. So, I have written this VBA code
VBA Code:
Sub MergeCells()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect "Password"
    Selection.Merge
    ActiveSheet.Protect "Password"
    Application.ScreenUpdating = True
End Sub
and this one
VBA Code:
Sub UnMergeCells()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect "Password"
    Selection.UnMerge
    ActiveSheet.Protect "Password"
    Application.ScreenUpdating = True
End Sub

My question is, I choose certain settings when applying the protection (like, allowing the users to insert rows, format cells etc.), the problem is, when I run the macro, these settings are removed.
Is there away to fix this? Like to run the macro and merge/unmerge cells and still have the protection settings the same?

Thanks in advance.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,664
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Record yourself setting the protection then use that code to replace
VBA Code:
ActiveSheet.Protect "Password"

You will have to add in the password so it will look something like
VBA Code:
ActiveSheet.Protect Password:="Password", DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True, AllowFormattingCells:=False, AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, _
        AllowSorting:=False, AllowFiltering:=False, AllowUsingPivotTables:=False
 

Yazzay

New Member
Joined
Jul 22, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Record yourself setting the protection then use that code to replace
VBA Code:
ActiveSheet.Protect "Password"

You will have to add in the password so it will look something like
VBA Code:
ActiveSheet.Protect Password:="Password", DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True, AllowFormattingCells:=False, AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, _
        AllowSorting:=False, AllowFiltering:=False, AllowUsingPivotTables:=False
Thank you that solved it!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,866
Messages
5,574,724
Members
412,616
Latest member
schabo
Top