Macro fails to work if Worksheet Protected with Password

[donggri84]

Hello,

I've got this VBA code below (mr excel members wrote it for me and it works great) for a worksheet.
However It does not work if worksheet is protected with password.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B23:HN23")) Is Nothing Then
If UCase(Target.Value) = "SAMPLE" Then
MsgBox ("Please fill the next column!")
End If
End If
On Error GoTo Err:
If Application.Intersect(Target, Range("B8")) = "New Zealand" Then
Rows("22:23").EntireRow.Hidden = True
End If

If Application.Intersect(Target, Range("B8")) = "Australia" Then
Rows("22:23").EntireRow.Hidden = False
End If
Err:

End Sub



Could anyone help me to have the code working with worksheet protection?

Thank you so much
Regards,
HJY

 

[Fishboy]

Hello,

I've got this VBA code below (mr excel members wrote it for me and it works great) for a worksheet.
However It does not work if worksheet is protected with password.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B23:HN23")) Is Nothing Then
If UCase(Target.Value) = "SAMPLE" Then
MsgBox ("Please fill the next column!")
End If
End If
On Error GoTo Err:
If Application.Intersect(Target, Range("B8")) = "New Zealand" Then
Rows("22:23").EntireRow.Hidden = True
End If

If Application.Intersect(Target, Range("B8")) = "Australia" Then
Rows("22:23").EntireRow.Hidden = False
End If
Err:

End Sub



Could anyone help me to have the code working with worksheet protection?

Thank you so much
Regards,
HJY

Hi donggri84,

You can add lines to the beginning and end of your code which will unprotect / reprotect the sheet. I suspect the finished code would look something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Unprotect "YOUR PASSWORD"
If Not Intersect(Target, Range("B23:HN23")) Is Nothing Then
If UCase(Target.Value) = "SAMPLE" Then
MsgBox ("Please fill the next column!")
End If
End If
On Error GoTo Err:
If Application.Intersect(Target, Range("B8")) = "New Zealand" Then
Rows("22:23").EntireRow.Hidden = True
End If

If Application.Intersect(Target, Range("B8")) = "Australia" Then
Rows("22:23").EntireRow.Hidden = False
End If
Err:
ActiveSheet.Protect "YOUR PASSWORD"
End Sub

 

[donggri84]

Hi donggri84,

You can add lines to the beginning and end of your code which will unprotect / reprotect the sheet. I suspect the finished code would look something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Unprotect "YOUR PASSWORD"
If Not Intersect(Target, Range("B23:HN23")) Is Nothing Then
If UCase(Target.Value) = "SAMPLE" Then
MsgBox ("Please fill the next column!")
End If
End If
On Error GoTo Err:
If Application.Intersect(Target, Range("B8")) = "New Zealand" Then
Rows("22:23").EntireRow.Hidden = True
End If

If Application.Intersect(Target, Range("B8")) = "Australia" Then
Rows("22:23").EntireRow.Hidden = False
End If
Err:
ActiveSheet.Protect "YOUR PASSWORD"
End Sub

PERFECT!
Thank you :D

 

[Fishboy]

I'm glad this was what you were looking for as I have really only been teaching myself all this since mid February. These forums have definitely been a goldmine of information for me so I am more than happy to share what I have been learning.