Is there a way to run a macro after pasting information into a cell?

For example excel lets you run things when you open the workbook:

Private Sub Workbook_Open()
End Sub

Is there one for paste as well?

Paste trigs the Worksheet_change event, but it can't tell whether you pasted
or wrote something into the cell. Unless you copy-paste a range of multiple
cells, then she knows that multiple cells changed at once.

HTH. Best wishes Harald

"RigasMinho" skrev i melding
ups.com...
Is there a way to run a macro after pasting information into a cell?

For example excel lets you run things when you open the workbook:

Private Sub Workbook_Open()
End Sub

Is there one for paste as well?

Harald,
Not exhaustively tested, but seems if you check the .CutCopyMode in this
event, you can detect a Paste as opposed normal entry.

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox IIf(Application.CutCopyMode = xlCopy, "Pasted", "Normal Entry")
End Sub

NickHK

"Harald Staff" wrote in message
...
Paste trigs the Worksheet_change event, but it can't tell whether you
pasted
or wrote something into the cell. Unless you copy-paste a range of
multiple
cells, then she knows that multiple cells changed at once.

HTH. Best wishes Harald

"RigasMinho" skrev i melding
ups.com...
Is there a way to run a macro after pasting information into a cell?

For example excel lets you run things when you open the workbook:

Private Sub Workbook_Open()
End Sub

Is there one for paste as well?

Hi Nick

It seem to work well for copy-paste, but not for cut-paste, which counts as
two normal entries in a serie. Neat idea though.

Best wishes Harald



"NickHK" skrev i melding
...
Harald,
Not exhaustively tested, but seems if you check the .CutCopyMode in this
event, you can detect a Paste as opposed normal entry.

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox IIf(Application.CutCopyMode = xlCopy, "Pasted", "Normal Entry")
End Sub

NickHK

"Harald Staff" wrote in message
...
Paste trigs the Worksheet_change event, but it can't tell whether you
pasted
or wrote something into the cell. Unless you copy-paste a range of
multiple
cells, then she knows that multiple cells changed at once.

HTH. Best wishes Harald

"RigasMinho" skrev i melding
ups.com...
Is there a way to run a macro after pasting information into a cell?

For example excel lets you run things when you open the workbook:

Private Sub Workbook_Open()
End Sub

Is there one for paste as well?

Harold,
After actually reading the help and seeing there are actually 3 values for
CutCopyMode, I thought would work:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg As String
Select Case Application.CutCopyMode
Case False
Msg = "Normal Entry"
Case xlCopy
Msg = "Pasted Copy"
Case xlCut
Msg = "Pasted Cut"
End Select
MsgBox Msg
End Sub

But xlCut is never fired and actually caused this event to fire twice. That
I could understand if there was a Cut (1 _Change) and a Paste (2 _Change),
but you are never (apparently) in CutCopyMode=xlCut for either operation,
both times =False. Seems a bit weird.

NickHK

"Harald Staff" wrote in message
...
Hi Nick

It seem to work well for copy-paste, but not for cut-paste, which counts
as
two normal entries in a serie. Neat idea though.

Best wishes Harald



"NickHK" skrev i melding
...
Harald,
Not exhaustively tested, but seems if you check the .CutCopyMode in this
event, you can detect a Paste as opposed normal entry.

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox IIf(Application.CutCopyMode = xlCopy, "Pasted", "Normal Entry")
End Sub

NickHK

"Harald Staff" wrote in message
...
Paste trigs the Worksheet_change event, but it can't tell whether you
pasted
or wrote something into the cell. Unless you copy-paste a range of
multiple
cells, then she knows that multiple cells changed at once.

HTH. Best wishes Harald

"RigasMinho" skrev i melding
ups.com...
Is there a way to run a macro after pasting information into a cell?

For example excel lets you run things when you open the workbook:

Private Sub Workbook_Open()
End Sub

Is there one for paste as well?

Weird indeed. Looks like something the programmers abandoned before it was
finished. To be useful, it would also need a fourth value; drag-drop of
cells or ranges.

Best wishes Harald

"NickHK" skrev i melding
...
Harold,
After actually reading the help and seeing there are actually 3 values for
CutCopyMode, I thought would work:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg As String
Select Case Application.CutCopyMode
Case False
Msg = "Normal Entry"
Case xlCopy
Msg = "Pasted Copy"
Case xlCut
Msg = "Pasted Cut"
End Select
MsgBox Msg
End Sub

But xlCut is never fired and actually caused this event to fire twice.
That
I could understand if there was a Cut (1 _Change) and a Paste (2 _Change),
but you are never (apparently) in CutCopyMode=xlCut for either operation,
both times =False. Seems a bit weird.

NickHK

Harold,
This seems better:

Dim Cutting As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg As String

Select Case Application.CutCopyMode
Case False
If Cutting Then
Msg = "Was Cut"
Else
Msg = "Normal Entry"
End If
Case xlCopy
Msg = "Pasted Copy"
End Select

MsgBox Msg

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cutting = (Application.CutCopyMode = xlCut)
End Sub

NickHK

"Harald Staff" wrote in message
...
Weird indeed. Looks like something the programmers abandoned before it was
finished. To be useful, it would also need a fourth value; drag-drop of
cells or ranges.

Best wishes Harald

"NickHK" skrev i melding
...
Harold,
After actually reading the help and seeing there are actually 3 values
for
CutCopyMode, I thought would work:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg As String
Select Case Application.CutCopyMode
Case False
Msg = "Normal Entry"
Case xlCopy
Msg = "Pasted Copy"
Case xlCut
Msg = "Pasted Cut"
End Select
MsgBox Msg
End Sub

But xlCut is never fired and actually caused this event to fire twice.
That
I could understand if there was a Cut (1 _Change) and a Paste (2
_Change),
but you are never (apparently) in CutCopyMode=xlCut for either
operation,
both times =False. Seems a bit weird.

NickHK

Thanks guys - i only needed it for copy because the user cant cut - the
worksheet is locked :-)

THis helps a lot.

NickHK wrote:
Harold,
This seems better:

Dim Cutting As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg As String

Select Case Application.CutCopyMode
Case False
If Cutting Then
Msg = "Was Cut"
Else
Msg = "Normal Entry"
End If
Case xlCopy
Msg = "Pasted Copy"
End Select

MsgBox Msg

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cutting = (Application.CutCopyMode = xlCut)
End Sub

NickHK

"Harald Staff" wrote in message
...
Weird indeed. Looks like something the programmers abandoned before it was
finished. To be useful, it would also need a fourth value; drag-drop of
cells or ranges.

Best wishes Harald

"NickHK" skrev i melding
...
Harold,
After actually reading the help and seeing there are actually 3 values
for
CutCopyMode, I thought would work:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg As String
Select Case Application.CutCopyMode
Case False
Msg = "Normal Entry"
Case xlCopy
Msg = "Pasted Copy"
Case xlCut
Msg = "Pasted Cut"
End Select
MsgBox Msg
End Sub

But xlCut is never fired and actually caused this event to fire twice.
That
I could understand if there was a Cut (1 _Change) and a Paste (2
_Change),
but you are never (apparently) in CutCopyMode=xlCut for either
operation,
both times =False. Seems a bit weird.

NickHK

Actually one more question:
right now i have a macro that runs and locks all the cells from Column
A-E
and unlocks everything below the last row.

So if you have column A-E filled with data until row 10
Row 1-10 are locked
Row 11- below are unlocked.

So now i have this where you lock the row as you paste in the
information. But somehow its not working right.

Any ideas?

i use:
below finds the last row and unlocks them
lRow = Worksheets(Sheet9.Name).Range("A65536").End(xlUp). Offset(1,
0).Row
Worksheets(Sheet9.Name).Range("A" & lRow & ":IV65536").Locked = False

below is the new code i want to run after you paste items in but i get
errors.
Dim Msg As String

Select Case Application.CutCopyMode
Case xlCopy
MsgBox "Pasted Copy"
Worksheets(Sheet10.Name).Cells(Rows.Count, 1).End(xlUp).Offset(0,
0).row.Locked = True
End Select


RigasMinho wrote:
Thanks guys - i only needed it for copy because the user cant cut - the
worksheet is locked :-)

THis helps a lot.

NickHK wrote:
Harold,
This seems better:

Dim Cutting As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg As String

Select Case Application.CutCopyMode
Case False
If Cutting Then
Msg = "Was Cut"
Else
Msg = "Normal Entry"
End If
Case xlCopy
Msg = "Pasted Copy"
End Select

MsgBox Msg

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cutting = (Application.CutCopyMode = xlCut)
End Sub

NickHK

"Harald Staff" wrote in message
...
Weird indeed. Looks like something the programmers abandoned before it was
finished. To be useful, it would also need a fourth value; drag-drop of
cells or ranges.

Best wishes Harald

"NickHK" skrev i melding
...
Harold,
After actually reading the help and seeing there are actually 3 values
for
CutCopyMode, I thought would work:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg As String
Select Case Application.CutCopyMode
Case False
Msg = "Normal Entry"
Case xlCopy
Msg = "Pasted Copy"
Case xlCut
Msg = "Pasted Cut"
End Select
MsgBox Msg
End Sub

But xlCut is never fired and actually caused this event to fire twice.
That
I could understand if there was a Cut (1 _Change) and a Paste (2
_Change),
but you are never (apparently) in CutCopyMode=xlCut for either
operation,
both times =False. Seems a bit weird.

NickHK

Nice. Good work NickHK.

Best wishes Harald

"NickHK" skrev i melding
...
Harold,
This seems better:

Dim Cutting As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg As String

Select Case Application.CutCopyMode
Case False
If Cutting Then
Msg = "Was Cut"
Else
Msg = "Normal Entry"
End If
Case xlCopy
Msg = "Pasted Copy"
End Select

MsgBox Msg

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cutting = (Application.CutCopyMode = xlCut)
End Sub

NickHK