Excel VBA protect returning error

0
votes

I've seen many posts on here and elsewhere about using the following code in the ThisWorkbook Object in order to protect your workbook but still allow macros to run:

Private Sub Workbook_Open()
    ActiveWorkbook.Protect UserInterfaceOnly:=True
End Sub

However, the protect function tooltip that pops up only offers three arguments Protect([password], [structure], [windows]). Therefore, when I open the document I get the following error

"Compile Error: Named argument not found"

in regard to UserInterfaceOnly:=True.

Am I using the wrong protect function, or is there something else I'm missing? I want to allow users to input data into the workbook using forms/macros, but I don't want them to be able to manually change any cells.

It almost seems as if the protect function that I'm running has different arguments than many of the other protect solutions I've found. Could this be a version issue or something else?

1
vbaexcel
Possible duplicate of VBA Excel: Sheet protection: UserInterFaceOnly gone – Our Man in Bananas
I'm asking something different. I already have the code in the Workbook_Open, however, VBA doesn't seem to be recognizing the UserInterfaceOnly:=True argument at all. – M. Terry
That argument is for worksheets, not the workbook. – Tim Williams
Okay, so is the solution to repeat this code at Workbook_Open for each sheet I need protected? – M. Terry
Yes - that's correct. – Tim Williams

1 Answers

2
votes

The suggestion provided by Tim Williams worked. Protect doesn't work the same way at the workbook level, therefore I needed to write individual code for each sheet I wanted to protect. I used the following code to protect the sheets but allow forms to populate:

Private Sub Workbook_Open()
    Worksheets("Sheet1").Protect UserInterfaceOnly:=True
    Worksheets("Sheet2").Protect UserInterfaceOnly:=True
    Worksheets("Sheet3").Protect UserInterfaceOnly:=True
End Sub