Create an alert in Excel when selecting multiple pages to prevent accidentally overwriting cells

Question

I am trying to write some Visual Basic code so that someone does not accidentally rewrite cells on multiple sheets when selecting multiple sheets.

However, I need the option to overwrite cells on multiple sheets, if necessary at any stage.

So, when I have selected several sheets, I would like to pop up with two options: "Are you sure you want to rewrite the cells on the sheets that you selected?" Ok Cancel

I think I'm almost there with the code below, but if I have 3 sheets, a popup will appear 3 times (once for each page). Naturally, I want the popup to be displayed once no matter how many sheets I have selected.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If ActiveWindow.SelectedSheets.Count > 1 Then If MsgBox("Are you sure you want to overwrite the cells across the sheets you have selected?", vbOKCancel) = vbCancel Then Exit Sub Application.EnableEvents = False Application.Undo End If Application.EnableEvents = True End Sub 

Or even the best solution actually:

"Do you really want to rewrite the cells to the sheets you selected?"

Yes (to continue all selected pages),

No (to select the current page and continue),

Cancel (to cancel the operation and save the current selection).

Answer 1

This solution checks if the event worksheet is an active worksheet to start the multiple selection procedure.

Also, if the user decides to update only the active sheet, the procedure leaves all other sheets included in the selection, as they were before the action that caused the ventilation hole, instead of the undesirable effect of entering the value vbNullString in all these cells

 Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Application.EnableEvents = False If Sh.Name = ActiveSheet.Name Then Call Wsh_MultipleSelection(Target) Application.EnableEvents = True End Sub Private Sub Wsh_MultipleSelection(ByVal rTrg As Range) Const kTtl As String = "Selection Across Multiple Sheets" Const kMsg As String = "You are trying to overwrite cells across multiple sheets." & vbLf & _ "Press [Yes] if you want to continue and overwrite the selected cells" & vbLf & _ "Press [No] if you want to overwrite selected cells in active sheet only" & vbLf & _ "Press [Cancel] to undo last action." Const kBtt As Long = vbApplicationModal + vbQuestion + vbYesNoCancel + vbDefaultButton3 Dim iResp As Integer Dim vCllVal As Variant Dim bWshCnt As Byte bWshCnt = ActiveWindow.SelectedSheets.Count If bWshCnt > 1 Then bWshCnt = -1 + bWshCnt iResp = MsgBox(kMsg, kBtt, kTtl) Select Case iResp Case vbYes Rem NO ACTION! Case vbNo: Rem Select Only Active Sheet vCllVal = rTrg.Cells(1).Value2 Application.Undo rTrg.Value = vCllVal Case Else Rem Cancel Application.Undo End Select: End If End Sub 

Answer 2

This is very difficult, because with the help of the Workbook_SheetChange event, the code will fire for each instance of a sheet change that you need to consider.

However, using some tricky use of public variables to use as a switch / counter and a separate routine to handle cases where you need to change everything against active or not worksheets, I developed a code that was thoroughly tested. I also commented very much on my code to understand the logic.

 Option Explicit Dim bAsked As Boolean Dim dRet As Double Dim iCnt As Long Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Application.EnableEvents = False Dim lSheets As Long lSheets = ActiveWindow.SelectedSheets.Count If lSheets > 1 Then Check lSheets, Sh, Target Application.EnableEvents = True End Sub Sub Check(iTotal As Long, ws As Worksheet, rng As Range) 'use this is a counter to count how many times the sub has been called in the firing of the 'Workbook_SheetChange` event iCnt = iCnt + 1 'if the question has not been asked yet (first time event is fired) If Not bAsked Then dRet = MsgBox("Are you sure you want to overwrite the cells across the sheets you have selected? Click Yes to overwrite all sheets, No to overwrite the Active Sheet, or Cancel to abort the entire overwrite.", vbYesNoCancel) bAsked = True 'set to true so question will only be asked once on event firing End If 'dRet will always be the same for each instance an event is fired Select Case dRet Case Is = vbYes 'set the value for each range to what user entered ws.Range(rng.Address) = rng.Value2 Case Is = vbNo 'only set the value the user entered to the active worksheet (the one the user is on) If ActiveSheet.Name = ws.Name Then ws.Range(rng.Address) = rng.Value2 Else ws.Range(rng.Address) = vbNullString End If Case Is = vbCancel 'do not set any values on any sheet Application.Undo End Select 'if the total times the sub has been called is equal to the total selected worksheet reset variables so they work next time 'if the count equals the total it the last time the sub was called which means its the last sheet If iCnt = iTotal Then bAsked = False iCnt = 0 End If End Sub