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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,587
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,186
Messages
5,570,750
Members
412,340
Latest member
nikitesh95
Top