Excel- Controlling worksheet protection via VBA


Joined
Jun 1, 2010
Messages
2
Reaction score
0
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
 
Ad

Advertisements

Joined
May 6, 2010
Messages
4
Reaction score
0
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top