Warning when editing multiple worksheets at once

0

Is there a way to be notified when you, knowingly or, unknowingly are editing multiple sheets, when you press F2, thereby alerting you there are multiple sheets being edited?

Answer
Discuss

Answers

0

Install the code below in a standard code module of your workbook. (Alt+F11 to open the VB Editor. Right-click on the project name listed on the left and choose Insert > Module. Paste in the window that opens on the right.)

Option Explicit
Sub MultipleSelectionAlert()
    ' 26 Apr 2018
    
    Static Warning As Boolean
    Dim Count As Integer
    
    Count = ActiveWindow.SelectedSheets.Count
    If Count > 1 Then
        If Not Warning Then
            If MsgBox("You are about to modify " & Count & " sheets." & vbCr & _
                   "Do you wish to continue?" & vbCr & _
                   "Click ""No"" to undo the change just made.", _
                   vbYesNo, "Multiple sheets selected") = vbNo Then
                With Application
                    .EnableEvents = False
                    .Undo
                    .EnableEvents = True
                End With
            End If
        End If
        Warning = True
    Else
        Warning = False
    End If
End Sub

Paste the following code in the code sheet of each of the worksheets in your workbook. (Open the code sheet by double-clicking on the sheet's name in the VBE's Project Explorer window on the left.)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    ' 26 Apr 2018
    MultipleSelectionAlert
End Sub

Now, a warning will be issued when you make a change in any of the sheets that have the second code. It will be shown once and will not be repeated until you have selected a single sheet and then again multiple sheets.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login