How do I access the source cells in Workbook_SheetChange

A

Arifi Koseoglu

Hello again ..

Is there a way to access the "source" (i.e. the copied/cut cells) from
within the "Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)"
procedure along with the target cells? As I see there is no version which
also gets the source range passed.

MTIA
-arifi
 
B

Bob Phillips

What source cells? This event is only triggered when a cell is selected, so
there is no copied/cut. If you want to copy from one cell to another, trap
the selection of the original cell and do the copy paste in there.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tom Ogilvy

there is no built in method to determine the source of the range in the
clipboard.
 
B

Bob Phillips

But you can save it each time, like so

Public oldCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not oldCell Is Nothing Then MsgBox oldCell.Address
Set oldCell = Target
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

Arifi Koseoglu

I understand. Thanks folks.

Within the limits of what we have been discussing in this thread, is there
something I might have done that would disable the "Copy" in the Edit menu
but leave Ctrl-C + Ctrl-V functioning?

I have been playing with Workbook_SheetChange and
Workbook_SheetSelectionChange in as simple a way as it gets (silly source
below), and at some stage the Copy entry in the menu must have been
disabled. I am about to jump out of the window. I commented every line out -
no change.

?!?!

'Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'
' Dim UndoButtonCaption As String
' UndoButtonCaption =
Application.CommandBars(1).Controls(2).Controls(1).Caption
'
' If UndoButtonCaption = "&Undo Paste" Then
' Dim rowCount As Integer
' Dim colCount As Integer
'
' colCount = Target.Columns.Count
' rowCount = Target.Rows.Count
'
' Call MsgBox(rowCount * colCount)
' End If
'
'End Sub

'Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
'
' Dim rowCount As Integer
' Dim colCount As Integer
'
' colCount = Target.Columns.Count
' rowCount = Target.Rows.Count
'
' Call MsgBox(rowCount * colCount)
'
'End Sub
 
Top