How do I access the source cells in Workbook_SheetChange

  • Thread starter Thread starter Arifi Koseoglu
  • Start date Start date
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
 
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)
 
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)
 
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
 
Back
Top