當新資料粘貼到指定范圍（A15：E33）時，我有下面的代碼創建一個彈出視窗。我想要的是當用戶嘗試將資料粘貼到彈出視窗顯示的范圍內時，如果用戶選擇否，則不會粘貼資料，以防止意外覆寫。
當前，當用戶選擇“否”時，它所做的只是防止單元格 B2 被添加時間戳。
預先感謝您的幫助
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$15:$E$33" Then
Dim answer As Integer
answer = MsgBox("You are about to overwrite existing data, would you like to continue?", vbQuestion vbYesNo)
If answer = vbYes Then
Range("B2") = "=NOW()"
Range("B2").Copy
Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("a15:e33").Select
Else
MsgBox "Cancelled"
End If
End If
End Sub
uj5u.com熱心網友回復：
您的代碼無法知道您打算在特定范圍內粘貼......
上面的代碼是一個事件，粘貼完成后自動觸發。你可以做的是使用Application.UnDo：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = "$A$15:$E$33" Then
Dim answer As VbMsgBoxResult
answer = MsgBox("You are about to overwrite existing data, would you like to continue?", vbQuestion vbYesNo)
If answer = vbYes Then
Application.EnableEvents = False 'to avoid the event being triggered again...
Range("B2") = "=NOW()"
Range("B2").Copy
Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("a15:e33").Select
Application.EnableEvents = True
Else
Application.EnableEvents = False 'to avoid the event being triggered again...
Application.Undo
Application.EnableEvents = True
MsgBox "Cancelled"
End If
End If
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/534671.html
標籤：擅长VBA消息框数据安全
上一篇：函式檢查作業表是否存在