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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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