CODEWITHSUNDEEP
vbaexcelruntime-errorpassword-protection
Steve Wright
Steve Wright

Reputation: 33

Runtime 1004 Workaround - Protect/Unprotect in Worksheet_Change

I've read a few others which partially resolved my issue but being a complete VB amateur I can't get this to work. The worksheet in question is protected so have tried adding in a protect/unprotect command in the code. It will unprotect fine at the start but then encounters problems. Any help would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

    Sheet1.Unprotect Password:="mypassword"

    If Target.Cells.Count > 1 Then Exit Sub

        If Not Intersect(Target, Range("B11")) Is Nothing Then
        Select Case Target.Value
            Case Is = ""
                Target.Value = "Product Name (IE Product123)"
                Target.Font.ColorIndex = 15
            Case Else
                Target.Font.ColorIndex = 1

        End Select
    End If

    If Not Intersect(Target, Range("B12")) Is Nothing Then
        Select Case Target.Value
            Case Is = ""
                Target.Value = "Version "
                Target.Font.ColorIndex = 15
            Case Else
                Target.Font.ColorIndex = 1

        End Select
    End If

    Sheet1.Protect Password:="mypassword"

End Sub

Upvotes: 3

Views: 1576

Answers (1)

user4039065
user4039065

Reputation:

You have not turned off the Application.EnableEvents property but there is a chance that you will write something to the worksheet. This would retrigger the event handler and the Worksheet_Change event macro would try to run on top of itself.

There is nothing preventing someone from simultaneously clearing the contents of both B11 and B12. Rather than abandoning the processing, accommodate the possibility and process both cells if there are two cells in target.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B11:B12")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        'turn off event handling 'cause we might write something
        Application.EnableEvents = False
        'why this unprotect necessary??
        'Me.Unprotect Password:="mypassword"
        Dim rng As Range
        For Each rng In Intersect(Target, Range("B11:B12"))
            Select Case rng.Value2
                Case vbNullString
                    If rng.Address(0, 0) = "B11" Then
                        rng = "Product Name (IE Product123)"
                    Else
                        rng = "Version "  '<~~ why the trailing space??
                    End If
                    rng.Font.ColorIndex = 15
                Case Else
                    rng.Font.ColorIndex = 1
            End Select
        Next rng
    End If

bm_Safe_Exit:
    'if unprotect is not necessary, neither is protect
    'Me.Protect Password:="mypassword"
    Application.EnableEvents = True

End Sub

You might also want to look into the UserInterfaceOnly parameter of the Worksheet.Protect method. Setting this to true allows you to do anything you want in VBA without unprotecting the worksheet.

Addendumm:

If the user can alter the contents of B11:B12 then these cells must not be locked. If they are not locked then there is no need to unprotect the worksheet before (possibly) altering their contents.

Upvotes: 2

Related Questions