VBA To Protect Sheet With Certain Permissions

data808

Active Member
Joined
Dec 3, 2010
Messages
358
Office Version
  1. 2019
Platform
  1. Windows
Right now I am using this to protect a sheet in my VBA code:

ActiveSheet.Protect Password:="hello"

But it removes all access to the Home tab in the ribbon. I would like the user to still be able to use the font color and size controls. Along with inserting/deleting rows. Does anyone know how to do this?

Thanks.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try this

All of these accesses are useful for doing anything in the protected activesheet, and if you don't want any of those accesses to be done in the protected activesheet change "TRUE" to "FALSE"
change it as you wish


VBA Code:
ActiveSheet.Protect Password:="hello", DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True


*If there’s any unclear information please let me know :)
 
Upvote 0
Solution
Try this

All of these accesses are useful for doing anything in the protected activesheet, and if you don't want any of those accesses to be done in the protected activesheet change "TRUE" to "FALSE"
change it as you wish


VBA Code:
ActiveSheet.Protect Password:="hello", DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True


*If there’s any unclear information please let me know :)
Works like a charm. Thank you so much.
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top