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?

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" <[email protected]> skrev i melding
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> skrev i melding
> news:[email protected]...
> > 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" <[email protected]> skrev i melding
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> skrev i melding
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> skrev i melding
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> skrev i melding
> >> news:[email protected]...
> >> > 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" <[email protected]> skrev i melding
news:%[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> skrev i melding
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> skrev i melding
> > news:%[email protected]...
> > > 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" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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" <[email protected]> skrev i melding
> > > news:%[email protected]...
> > > > 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" <[email protected]> skrev i melding
news:[email protected]...
> 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

Originally Posted by RigasMinho

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

not quite true: on a protected sheet, it's possible to cut from unlocked cells.