Protect Sheets and Allow Editing

[GomaPile]

Good morning World

VBA below Ive done something wrong. Could someone please have a look.... thanks in advance.

Private Sub Workbook_Open()
  
Dim ws As Variant, MyArray As Variant
 
MyArray = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
For Each ws In MyArray
    If UserIsAllowedToEdit Then
        ' Unprotect the sheet with password (if required)
        ws.Unprotect "test"
Next ws
    Else
        ws.Range("C:C").Locked = False ' Unlocked
        ws.Range("G:G").Locked = False ' Unlocked
        ws.Protect "test", UserInterfaceOnly:=True
    End If
UserForm3.Show    
End Sub

Cheers
GomaPile (NASA2)

 

[Joe4]

You cannot close out your For/Next loop until you have closed out the If/Then coding within it!
When you have multi-row blocks of code like that (for Loops, If/Then, With statements), you always need to close out the most current level before move to the next level up!

So the code would need to be structured something like this:

Private Sub Workbook_Open()
  
Dim ws As Variant, MyArray As Variant
 
MyArray = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
For Each ws In MyArray
    If UserIsAllowedToEdit Then
        ' Unprotect the sheet with password (if required)
        ws.Unprotect "test"
    Else
        ws.Range("C:C").Locked = False ' Unlocked
        ws.Range("G:G").Locked = False ' Unlocked
        ws.Protect "test", UserInterfaceOnly:=True
    End If
Next ws

UserForm3.Show
    
End Sub

 

[GomaPile]

Hey Joe4,

Thank you so much for the clarification, still leaning heaps.

However, the vba still doesn't work properly. Opened a new Workbook made the changes, when it opens the vba stops at the point... highlighting the VBA Error in YELLOW.

ws.Range("C:C").Locked = False ' Unlocked

GomaPile (NASA2)

 

[MARK858]

Try..

WorkSheets(ws).Range("C:C").Locked = False

 

[GomaPile]

Hey Mark, thanks for your assistance too!!

Made those changes... same error again as before, see attachment.

What Im trying to achieve here is to protect an array of Sheets (mychoice) but allow user's the ability to enter data only in Columns C & G, while the Sheets are protected.

Tried searching the internet for any fix solution... but came up with nothing suitable.

Private Sub Workbook_Open()
  
Dim ws As Variant, MyArray As Variant
 
MyArray = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
For Each ws In MyArray
    If UserIsAllowedToEdit Then
        ' Unprotect the sheet with password (if required)
        Worksheets(ws).Unprotect "test"
    Else
        Worksheets(ws).Range("C:C").Locked = False  '  <---- VBA Error stops here  
        Worksheets(ws).Range("G:G").Locked = False
        Worksheets(ws).Protect "test", UserInterfaceOnly:=True
    End If
Next ws

'UserForm3.Show
    
End Sub

Cheers
GomaPile (NASA2)

 

[MARK858]

but allow user's the ability to enter data only in Columns C & G, while the Sheets are protected

Then you need to the adjust the individual protection settings for each task you need the user to carry out (options are below) when you set the protection
As for the error (where you haven't actually shown what the error message is) I can only guess that UserInterfaceOnly: is set to False when the code runs.

 

[Joe4]

Notice the structure of your code:

    If UserIsAllowedToEdit Then
        ' Unprotect the sheet with password (if required)
        Worksheets(ws).Unprotect "test"
    Else
        Worksheets(ws).Range("C:C").Locked = False  '  <---- VBA Error stops here

You are only unprotecting the sheet in the first part of the IF.
If that condition is not met, you are trying to change the "Locked" property of the range, but you haven't unprotected the sheet!
I would think you probably need to unprotect the sheet too in order to change that.