VBA Cut & paste issue

[Anton Jansen]

I have a spreadsheet with VBA code """Private Sub Worksheet_SelectionChange(ByVal Target As Range)""" while this module is listed in the code I cannot cut & paste data to the sheet. When I mark all this code as a comment, i.e. the SUB is not active, cut & paste works.

 

[Joe4]

Who put the code in there?
What is is supposed to do?
Can you copy/paste the code here (using the "Code Tags") so we can see it?

 

[Anton Jansen]

Who put the code in there?
What is is supposed to do?
Can you copy/paste the code here (using the "Code Tags") so we can see it?

Hello, I placed the code into this spreadsheet. The code works and what it does is, it registers the contents of the cell clicked as there is a change to the spreadsheet. I have a list of numbers in a range and clicking on a cell e.g. 100 will be used for calculations and placed into another cell. All this code does is detect a change anywhere on the sheet and records the contents of the cell clicked. Here is the code;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long, siltype As String
If Not Intersect(Target, Range("B3")) Is Nothing Then Range("B7:B10").ClearContents
If Not Intersect(Target, Range("B4")) Is Nothing Then Range("B7:B10").ClearContents
If Not Intersect(Target, Range("B5")) Is Nothing Then Range("B7:B10").ClearContents
For i = 2 To 11
If Target.Count = 1 Then
If Not Intersect(Target, Range(("F") & i)) Is Nothing Then
Range("B7") = Range(("E") & i)
Range("B8") = Range(("F") & i)
Range("B9") = Range(("E") & i).Value + 1
Range("B10") = ""
End If

If Not Intersect(Target, Range(("G") & i)) Is Nothing Then
Range("B7") = Range(("E") & i)
Range("B8") = Range(("G") & i)
Range("B9").Value = 2
Range("B10") = Range(("E") & i).Value - 1
End If
End If
Next i
If Range("B10") = "" Then
siltype = " LSS "
Else
siltype = " LDS "
End If
Range("B1") = siltype & Range("B7") & "/" & Int(Range("B8")) & " - " & _
Range("B3") & "x" & Range("B4") & "x" & Range("B5")
If Range("B12") <> "" And Range("B7") <> "" And Range("B8") <> "" Then
Range("B14") = ((Range("B12").Value / Range("B7").Value)) / _
(Range("B4").Value * Range("B8").Value / 1000)
Range("B13") = Range("L14")
Else
Range("B13:B14").ClearContents
End If
End Sub

 

[Joe4]

Note that your code does NOT run on/detect changes. "Worksheet_SelectionChange" event procedure run every time you simply select a new cell (you don't need to make changes for it to run).
This kind of code may wreak havoc with things like cut & paste, as that involves selecting different cells (which will keep calling the code to run).

You will probably need to amend your code to "bail out" in certain situations, or create another procedure for cut/paste where you first disable events from being called, to prevent that code from running, i.e.
put

Application.EnableEvents = False

at the beginning to temporarily disable events from being called
and put

Application.EnableEvents = True

at the end of the code to re-enable them again.