Excel- Controlling worksheet protection via VBA

[Áine]

I have created a user form with VBA for data entry in an Excel worksheet. I would like to control what the user can modify and so have protected the cells.

After filling out the form I would like my VBA code to return the worksheet to its previously protected state.

I have tried to control the password protection on my worksheet via VBA using the following code:

Public Sub Protect()
Worksheets("myWorksheet").Protect Password:="password", _
UserInterfaceOnly:=True
End Sub

Public Sub UnProtect()
Worksheets("myWorksheet").UnProtect Password:="password"
End Sub

This works okay in that it reprotects the sheet, the only problem is that the level of protection is too high. I had previously allowed formatting of columns, rows and cells but now the sheet is completely locked.

Is there any way to pass an argument into this protect subroutine so that I can control the level of protection?

Thanks

 

[lowtechman]

Yah, you can try these arguments of the protect function


Dim ws As Excel.Worksheet
Set ws = Sheets("Sheet1")
ws.Protect AllowDeletingColumns:=True, AllowFormattingCells:=True

The object browser lists all of these arguments that can be set to true/false

[UserInterfaceOnly], [AllowFormattingCells], [AllowFormattingColumns], [AllowFormattingRows], [AllowInsertingColumns], [AllowInsertingRows], [AllowInsertingHyperlinks], [AllowDeletingColumns], [AllowDeletingRows], [AllowSorting], [AllowFiltering], [AllowUsingPivotTables]

Hope this helps