Run Macro After Paste
 

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?

Run Macro After Paste
 

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?

Run Macro After Paste
 

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?

Run Macro After Paste
 

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?

Run Macro After Paste
 

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?

Run Macro After Paste
 

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

Run Macro After Paste
 

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

Run Macro After Paste
 

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

Run Macro After Paste
 

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

Run Macro After Paste
 

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