My VBA Code will not work when the sheet is protected?

Question

My VBA Code will not work when the sheet is protected?

Anonymous

I'm using the following VBA code...

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B:H")) Is Nothing Then

If Target.Count > 1 Then Exit Sub

Cells(Target.Row, "M").Value = Environ("username") & ", " & Format(Now, "yyyy/mm/dd hh:mm:ss")

End If

End Sub

It works fine if I leave the sheet unprotected. But when I protect the sheet in order to lock specific cells, I get a Debugger message box.

Do I need to alter the VBA code to allow it to work when the sheet is protected?

Many thanks.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.7K MVP Volunteer Moderator

Like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("B:H")) Is Nothing Then

Application.EnableEvents = False

Me.Unprotect 'Password:="Secret"

Cells(Target.Row, "M").Value = Environ("username") & ", " & _

Format(Now, "yyyy/mm/dd hh:mm:ss")

Me.Protect 'Password:="Secret"

Application.EnableEvents = True

End If

End Sub

If you applied a password, replace "Secret" with the password that you used, and remove the apostrophe ' from before both occurrences of Password:=...

0 comments

2 additional answers

Answer 1

Like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("B:H")) Is Nothing Then

        Application.EnableEvents = False

        Me.Unprotect 'Password:="Secret"

        Cells(Target.Row, "M").Value = Environ("username") & ", " & _

            Format(Now, "yyyy/mm/dd hh:mm:ss")

        Me.Protect 'Password:="Secret"

        Application.EnableEvents = True

    End If

End Sub

If you applied a password, replace "Secret" with the password that you used, and remove the apostrophe ' from before both occurrences of Password:=...

Having the same issue with a different VBA code. Applied your recommendation but it still won't work with my code. works great unlocked. Thanks in advance.

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)

'Update 20140923

Static xLastRng As Range

On Error Resume Next

Target.Interior.ColorIndex = 6

xLastRng.Interior.ColorIndex = xlColorIndexNone

Set xLastRng = Target

Me.Protect 'Password:=mark

Application.EnableEvents = True

End Sub

Answer 2

Anonymous

That's done the trick perfect!

You have my sincere thanks HansV!

0 comments