VBA Select and Copy Not Working After Adding Locked/Unlocked Cells in Excel

Question

I created an Excel Sheet that has a Command Button (Active X Control). The button selects and copies cells e2:e16,e106:e117.

I also added code to lock or unlock cells C112 and C116 depending on cell C3. If C3 if "1" the cells stay unlocked and if "2" the cells lock.

The sheet starts protected so most cells are locked except ones that need data entered into them.

When ActiveSheet.Protect "" is commented out it copies correctly, but the sheet does not protect itself after I change C3 to "1" or "2".

If 'ActiveSheet.Protect "" is not commented out the lock/unlock works but it tries to select and copy cell C3 and ignores the range it's suppose to select and copy.

I would like both to work together.

Code and Excel Sheets

Top left 'ActiveSheet.Protect commented out.

Top right after hitting the Copy Narrative button it selects and copies the correct area but entire sheet not protected.

Bottom left ActiveSheet.Protect not commented out.

Bottom right after hitting Copy Narrative it selects and copies cell C3.

Select and Copy Cells:

Private Sub copyButton_Click()

    'Check for blanks'
    If Range("c3").Value = "" Or _
        Range("c6").Value = "" Or _
        Range("c7").Value = "" Or _
        Range("c8").Value = "" Or _
        Range("c9").Value = "" Or _
        Range("c10").Value = "" Or _
        Range("c108").Value = "" Or _
        Range("e16").Value = "" Or _
        Range("e106").Value = "" Or _
        Range("e115").Value = "" Then
        MsgBox "Required fields are blank."
    Exit Sub
    Else

    'Copy Ranges
    Range("e2:e16,e106:e117").Select
    Selection.Copy

    End If

End Sub

Lock/Unlock Cells:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   'Change cells locked/unlocked
     ActiveSheet.Unprotect ""
     If Range("C3").Value = "1" Then
      Range("C112,c116").Locked = False
     Else: Range("C3").Value = "2"
      Range("C112,c116").Locked = True
     End If
     ActiveSheet.Protect ""

End Sub

Sacru2red Sacru2red · Accepted Answer · 2020-02-12T05:27:45

I think you should

A. Unprotect before copy Range

Or

B. Don't call "Worksheet_SelectionChange" during "copyButton_Click" runing

blow code is Case B

Private Sub copyButton_Click()
    'Dont Call Sheet events
    Application.EnableEvents = false
    'Check for blanks'
    If Range("c3").Value = "" Or _
        Range("c6").Value = "" Or _
        Range("c7").Value = "" Or _
        Range("c8").Value = "" Or _
        Range("c9").Value = "" Or _
        Range("c10").Value = "" Or _
        Range("c108").Value = "" Or _
        Range("e16").Value = "" Or _
        Range("e106").Value = "" Or _
        Range("e115").Value = "" Then
        MsgBox "Required fields are blank."
    Exit Sub
    Else

    'Copy Ranges
    Range("e2:e16,e106:e117").Select
    Selection.Copy

    End If
    'rollBack
    Application.EnableEvents = True
End Sub

VBA Select and Copy Not Working After Adding Locked/Unlocked Cells in Excel

1 Answers