CODEWITHSUNDEEP
vbaexcel
Ian
Ian

Reputation: 107

Specifying User Permissions in VBA for Protected Excel Sheet

I am working with a spreadsheet that is required to be protected before distribution for the sheer sake of data integrity. I have written a function that auto-populates a column based on a drop down list selection. I do not want the user to edit this column so I have it protected, however in order to auto-populate my process un-protects and re-protects the spreadsheet. This is where the issue lies.

I would like users to have all other permissions (e.g. formatting, row insertion, row deletion, etc). However, when the process re-protects the sheet, all permissions are revoked.

Is there a way I can both lock the sheet AND specify which user permissions I would like to grant in VBA?

Upvotes: 1

Views: 3994

Answers (1)

user4039065
user4039065

Reputation:

The Worksheet.Protect method allows to specify everything that would be available to you when performing the Review ► Changes ► Protect Worksheet command. The parameters are largely optional so they need to be specified explicitly or blank parameters can be passed in with commas as placeholders.

To protect a worksheet with a password and allow column formatting and row insertion:

With Worksheets("Sheet One")
    .Protect Password:="myPassword", Contents:=True, _
             AllowFormattingColumns:=True, AllowInsertingRows:=True
    'insert a row
    .Rows("9:9").EntireRow.Insert CopyOrigin:=xlFormatFromLeftOrAbove
End With

See the Worksheet.Protect method for a full list of available options.

Another option is the UserInterfaceOnly. This stops the user from predetermined actions on the worksheet but allows VBA procedures to perform actions that would otherwise be restricted.

With Worksheets("Sheet One")
    .Protect Password:="myPassword", UserInterfaceOnly:=True, Contents:=True, _
                 AllowFormattingColumns:=True, AllowInsertingRows:=True
    'insert a column; the user cannot do this
    .Columns(2).EntireColumn.Insert CopyOrigin:=xlFormatFromLeftOrAbove
End With

This latter behavior allow you more freedom in your VBA procedures without having to continually unprotect and reprotect the worksheet.

Upvotes: 3

Related Questions