Excel VBA (Force Paste Special): Cannot capture sheet change events correctly & issue with sendkeys

narendra

Board Regular
Joined
Apr 15, 2008
Messages
87
Hi Friends,

I am trying to use below VBA code to force paste special operation when a user performs cut/copy & paste.
I am having 2 issues here:
1. The sheet change event triggers only the first-time (after opening the workbook) and thereafter it does not.
2. When (the first time) the code is triggered, it does not work correctly, it simply writes "suf" in the target cell, which is a later part of paste-special short-cut key (using sendkeys in my below code).
Please Note: To avoid the 2nd issue, I added half-second pause between sendkeys, but it does not work :(

Following Declaration is in code module:
Code:
#If VBA7 Then ' Excel 2010 or later
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
#Else ' Excel 2007 or earlier
    Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
#End If

and below VBA code goes in Sheet Module:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

'Get the last undo action performed by the user
UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

'If last action was not a paste, skip the operation
If Left(UndoList, 5) <> "Paste" Then GoTo SkipPasteSpecial

'This method of PasteSpecial keeps the undo buffer
Application.SendKeys "^z", True 'Undo paste performed by the user
Sleep 1000
Application.SendKeys "%e", True 'Not using CTRL+SHIFT+V (some macros use it for customized operations)
Sleep 1000
Application.SendKeys "s", True 'Loads pastespecial dialog box
Sleep 1000
Application.SendKeys "u", True 'Read Trick1 below
Sleep 1000
Application.SendKeys "f", True 'Read Trick2 below 'Change "f" to "v" to paste as value instead of formula.
Sleep 1000
Application.SendKeys "{ENTER}", True 'Apply paste special
Sleep 1000
'Trick1: if copy source is external, select "Unicode Text" format (use t for "Text" format)
'Trick2: Changes selection to formula/values, if copy source is excel. If not this keypress changes nothing

SkipPasteSpecial:
Application.EnableEvents = True

End Sub

FYI, earlier, I tried to use above code on a different workbook but always ended up in a macro loop, may be it had something to do with sheet change event being triggered again and again. That workbook didn't have any other event macros, I still have no clue why it behaves differently on that file.

Someone please tell me what is wrong with the code. Please.
Thanks.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Watch MrExcel Video

Forum statistics

Threads
1,123,425
Messages
5,601,580
Members
414,460
Latest member
uctc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top