Msg Box Doesn't Appear - VBA Code

elenakotanchyan

New Member
Joined
Jul 13, 2023
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Web
Hi,

I have this VBA code. The pasting in the range E7:E504 is disabled but the message "You are not allowed to paste to range E7:E504" does not appear. How do I fix it?


Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngPaste As Range
Dim UndoList As String
Dim rngValidate As Range
Dim cell As Range

' See if any cells in range E6:E504 were updated
Set rngPaste = Intersect(Range("E7:E504"), Target)

' See if any cells in column G were updated
Set rngValidate = Intersect(Columns("G:G"), Target)

' Check for paste action in range E7:E504
If Not rngPaste Is Nothing Then

' Get the undo List to capture the last action performed by user
UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

' See if last action was paste
If Left(UndoList, 5) = "Paste" Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Application.CutCopyMode = False
MsgBox "You are not allowed to paste to range E7:E504"
End If
End If

'Check for validation in column G based on column E

If Not rngValidate Is Nothing Then
For Each cell In rngValidate
If cell.Value <> "" And cell.Offset(0, -2) = "" Then
Application.EnableEvents = False
cell.ClearContents
Application.EnableEvents = True
MsgBox "Please, make sure there is response in column E before providing response in column G ", _
vbOKOnly, "ENTRY ERROR!!!"
End If
Next cell
End If

Set rngCheck = Intersect(Range("E7:E504"), Target)
If Not rngCheck Is Nothing Then
For Each cell In rngCheck
If cell.Value = cell.Offset(o, 1).Value Then
Application.EnableEvents = False
cell.Value = ""
Application.EnableEvents = True
MsgBox "Cells in column E should be different from cells in column F.", -vbOKOnly
End If
Next cell
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRange As Range
Set myRange = Me.Range("E7:E504")

If Application.Intersect(Target, myRange) Is Nothing Then
Application.CellDragAndDrop = True
Else
Application.CellDragAndDrop = False
End If


With Sheet5.DTPicker1
.Height = 20
.Width = 20
If Not Intersect(Target, Range("H7:H504")) Is Nothing Then
.Visible = True
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.LinkedCell = Target.Address
Else
.Visible = False
End If
End With

End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Start by stepping through your code - that is troubleshooting 101. If the execution of a line depends on the value of a variable or logical test, make sure the result of that test is what you think it is.

It's best if you post code within code tags (vba button on posting toolbar) to maintain indentation and readability.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top