Running VBA code in a protected worksheet

[wdgor]

I have VBA code that produces the output below. When a user left mouse double clicks on a row in column A the code creates a copy of the line, changes the number to X+.1 and changes the color of the line. It also changes the Task Number to concatenate the Issue number with the Client. This all works perfectly until I try to protect the columns in yellow. When I use the Me.Protect code (see below), I get an error on the Target.Offset line. I am not a VBA expert and don't know how to correct the code to allow the code to run in a protected worksheet without getting an error (see below - Run-time error '1004' - Insert method of Range class failed). I don't understand why the code works perfectly in an unprotected sheet but fails with protection. Thanks in advance for any assistance.

 

[Joe4]

You are trying to insert a row on a protected sheet - that will not work.

There is a simple solution.
At the very beginning of your VBA code, unprotect the sheet (via the VBA code).
And then re-protect it again at the end of your VBA code.

See this thread for details on how to do that: VBA Protect / Unprotect Worksheets - Automate Excel

 

[wdgor]

Thanks for the suggestion. I tried to do an unprotect at the beginning of the code but get a Subscript out of range error.

 

[Joe4]

"Sheet5" is not the name of your sheet, "Master Worksheet" is.

 

[wdgor]

OMG - what a novice mistake. I changed the name and it appears all is well. THANKS!!

 

[Joe4]

You are welcome.
Since you are on a Sheet module anyhow, I think you could also just use "ActiveSheet".

 

[wdgor]

Well, I spoke too soon. There is also another macro that allows the user to do multiple selections from a selection set (Worksheet_Change) - those selection sets are not locked cells. I put the same unprotect code in that section and it causes an error in the macro above. If I don't include the unprotect code in the Change section, the insert row works fine but the multiple selections doesn't work. Thoughts?

 

[Joe4]

You need to be careful where you actually place those lines.
If you have sections of code where you are exiting the code before it gets finished (i.e. "Exit Sub"), you could exit the code before you re-protect your workbook.
You may need a more targeted approach where you place those lines just in the lines where you are making changes that need it to be unprotected.

If you still need help, tell us exactly what the error message you are getting is (in addition to the line that is returning that error).
And please all your code using VBA code tags, so we can easily and copy it ourselves to text your code (we cannot copy it from images!).
Here are instructions on how to post your code: How to Post Your VBA Code

 

[wdgor]

Thanks. I am not sure where I should put the unprotect code. It seems that it should be at the beginning and end of the code that runs. I tried just putting the unprotect at the end of the Change code but that threw an error. Sorry if I am being dense.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Worksheets("Master Worksheet").Unprotect
If Target.Column <> 1 Then
Cancel = False
Exit Sub
End If
If Target.Cells = "" Then
Cancel = False
Exit Sub
End If
Cancel = True
   Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = Target.Value + 0.1
    Set rngcurrent = ActiveCell
    Range("A" & rngcurrent.Row & ":AF" & rngcurrent.Row).Interior.ColorIndex = 37
    Cancel = False
    Worksheets("Master Worksheet").Protect
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets("Master Worksheet").Unprotect
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                If xValue1 = xValue2 Or _
                   InStr(1, xValue1, ", " & xValue2) Or _
                   InStr(1, xValue1, xValue2 & ",") Then
                    Target.Value = xValue1
                Else
                    Target.Value = xValue1 & ", " & xValue2
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
   Worksheets("Master Worksheet").Protect
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Worksheets("Master Worksheet").Unprotect
If Target.Column <> 1 Then
Cancel = False
Exit Sub
End If
If Target.Cells = "" Then
Cancel = False
Exit Sub
End If
Cancel = True
   Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = Target.Value + 0.1
    Set rngcurrent = ActiveCell
    Range("A" & rngcurrent.Row & ":AF" & rngcurrent.Row).Interior.ColorIndex = 37
    Cancel = False
  
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                If xValue1 = xValue2 Or _
                   InStr(1, xValue1, ", " & xValue2) Or _
                   InStr(1, xValue1, xValue2 & ",") Then
                    Target.Value = xValue1
                Else
                    Target.Value = xValue1 & ", " & xValue2
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
   Worksheets("Master Worksheet").Protect
End Sub

 

[Joe4]

OK, that did not quite work out right.

If you use the Code tags, after you paste the code, you want to select it all the code before clicking the code tags button (you have to select the lines of code you want to apply it to!).

EDIT: I went in as a Moderator and fixed the code tags.