选择多页时，在Excel中创建警告，以防止意外覆盖单元格 [英] Creating a warning in Excel when multiple pages are selected to prevent accidental overwriting of cells

问题描述

我正在尝试写一些Visual Basic代码，以防止任何人在选择多个工作表时意外覆盖多个工作表单元。

I am attempting to write a bit of Visual Basic code to prevent anyone from accidentally overwriting cells across multiple sheets when multiple sheets are selected.

在任何阶段都需要覆盖多个单元格的选项。

I do however want the option of overwriting cells across multiple sheets, should that be required at any stage.

所以，当我选择了多个工作表时，我想弹出2个选项，如如下：
您确定要覆盖所选表格中的单元格吗？
确定取消

So, when I have multiple sheets selected I would like a pop up with 2 options, as follows: "Are you sure you want to overwrite the cells across the sheets you have selected?" Ok Cancel

我认为我已经在下面的代码，但如果我有3张选择，那么弹出窗口将出现3次（一次为每页）。当然，只要弹出窗口出现一次，不管我选择了多少页。

I think I am nearly there with the code below, but if I have 3 sheets selected then the pop up will appear 3 times (once for each page). Naturally I only want the pop up to appear once regardless of 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 an even better solution would actually be:

你确定要覆盖所选表格中的单元格吗？

"Are you sure you want to overwrite the cells across the sheets you have selected?"

是（继续所有选定的页面），

Yes (to continue with all selected pages),

否（选择当前页面并继续）

No (to select current page and continue),

取消（取消操作并保持当前选择）。

Cancel (to cancel operation and keep current selection).

推荐答案

如果事件工作表是活动工作表以触发多重选择过程，则此解决方案将验证事件工作表。

This solution validates if the event worksheet is the active worksheet in order to fire the Multiple Selection procedure.

如果用户选择仅更新活动工作表，则过程将离开所有其他表格都包含在选择中，因为它们在触发通风口的动作之前，而不是在所有这些单元格中输入不期望的效果 vbNullString value

Also if user chooses to update only the active sheet, the procedure leaves all other sheets included in the selection as they were before the action that triggered the vent, instead of the undesired effect of entering in all those cell the vbNullString value

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

这篇关于选择多页时，在Excel中创建警告，以防止意外覆盖单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！