Microsoft Excel - Worksheet selection change causing code to run twice

Asked By John Wirth on 26-Nov-12 02:51 PM
I have written code to prevent users from selecting locked cells (sheet is not protected). It does this by moving to cell A1 which is not protected. The trouble is, it runs twice- once when a locked cell is selected, but again when the code selects A1. Code is in the workbook object.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target _
As Excel.Range)
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Tactical Planner")
MsgBox ActiveCell.AddressLocal
If ws.Name = ActiveWorkbook.ActiveSheet.Name And ActiveCell.Locked = True Then
MsgBox "To change shift information, please use the global TP. This local TP is linked to the global and will update. Use this TP for entering each days duties and tasks"
Range("A1").Select
End If
End Sub
Harry Boughen replied to John Wirth on 26-Nov-12 03:03 PM
Hello John,

Perhaps you need EnableEvents = False .... EnableEvents = True around the offending section of code.

Regards

Harry
John Wirth replied to Harry Boughen on 30-Nov-12 05:42 AM
Thanks Harry. As it turns out I had left the cell locked which is why the code was running twice. It works fine now.