+ Reply to Thread
Results 1 to 11 of 11

Run Macro After Paste

  1. #1
    RigasMinho
    Guest

    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?


  2. #2
    Harald Staff
    Guest

    Re: 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" <minho.cho@gmail.com> skrev i melding
    news:1155670936.817835.77020@m79g2000cwm.googlegroups.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?
    >




  3. #3
    NickHK
    Guest

    Re: 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" <innocent@enron.invalid> wrote in message
    news:utq9ZdKwGHA.1956@TK2MSFTNGP02.phx.gbl...
    > 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" <minho.cho@gmail.com> skrev i melding
    > news:1155670936.817835.77020@m79g2000cwm.googlegroups.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?
    > >

    >
    >




  4. #4
    Harald Staff
    Guest

    Re: 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" <TungCheWah@Invalid.com> skrev i melding
    news:e4c4hZOwGHA.4140@TK2MSFTNGP06.phx.gbl...
    > 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" <innocent@enron.invalid> wrote in message
    > news:utq9ZdKwGHA.1956@TK2MSFTNGP02.phx.gbl...
    >> 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" <minho.cho@gmail.com> skrev i melding
    >> news:1155670936.817835.77020@m79g2000cwm.googlegroups.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?
    >> >

    >>
    >>

    >
    >




  5. #5
    NickHK
    Guest

    Re: 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" <stf@enron.invalid> wrote in message
    news:%23LZ3FeRwGHA.1284@TK2MSFTNGP05.phx.gbl...
    > 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" <TungCheWah@Invalid.com> skrev i melding
    > news:e4c4hZOwGHA.4140@TK2MSFTNGP06.phx.gbl...
    > > 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" <innocent@enron.invalid> wrote in message
    > > news:utq9ZdKwGHA.1956@TK2MSFTNGP02.phx.gbl...
    > >> 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" <minho.cho@gmail.com> skrev i melding
    > >> news:1155670936.817835.77020@m79g2000cwm.googlegroups.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?
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >




  6. #6
    Harald Staff
    Guest

    Re: 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" <TungCheWah@Invalid.com> skrev i melding
    news:%23sCNBvRwGHA.3364@TK2MSFTNGP02.phx.gbl...
    > 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




  7. #7
    NickHK
    Guest

    Re: 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" <stf@enron.invalid> wrote in message
    news:%23Tq5W7RwGHA.1512@TK2MSFTNGP04.phx.gbl...
    > 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" <TungCheWah@Invalid.com> skrev i melding
    > news:%23sCNBvRwGHA.3364@TK2MSFTNGP02.phx.gbl...
    > > 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

    >
    >




  8. #8
    RigasMinho
    Guest

    Re: 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" <stf@enron.invalid> wrote in message
    > news:%23Tq5W7RwGHA.1512@TK2MSFTNGP04.phx.gbl...
    > > 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" <TungCheWah@Invalid.com> skrev i melding
    > > news:%23sCNBvRwGHA.3364@TK2MSFTNGP02.phx.gbl...
    > > > 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

    > >
    > >



  9. #9
    RigasMinho
    Guest

    Re: 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" <stf@enron.invalid> wrote in message
    > > news:%23Tq5W7RwGHA.1512@TK2MSFTNGP04.phx.gbl...
    > > > 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" <TungCheWah@Invalid.com> skrev i melding
    > > > news:%23sCNBvRwGHA.3364@TK2MSFTNGP02.phx.gbl...
    > > > > 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
    > > >
    > > >



  10. #10
    Harald Staff
    Guest

    Re: Run Macro After Paste

    Nice. Good work NickHK.

    Best wishes Harald

    "NickHK" <TungCheWah@Invalid.com> skrev i melding
    news:uzqMqRSwGHA.4576@TK2MSFTNGP03.phx.gbl...
    > 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




  11. #11
    Registered User
    Join Date
    12-10-2010
    Location
    bayview
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Run Macro After Paste

    Quote Originally Posted by RigasMinho View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1