Run VBA code after paste

[markwilliams12]

I have a UDF that logs actions in one of my excel sheets. It works great, except that it isn't aware when I (or another user) copies and pastes a range of cells to another range of cells.


I would have hoped that MS would have included a AfterPaste Worksheet Event, but this doesn't exist.


Does anyone know how to execute VBA code after a users pastes data?

 

[SirJB7]

Hi, Marktastic!

Tried using the worksheet change event with checking for proper ranges?

Regards!

 

[markwilliams12]

Thanks!


I'm using the Workbook_SheetSelectionChange to capture when a cell was changed. I'll be expanding that to capture values of the range selected to an array. The calling of my LogChange Module is done via Workbook_SheetChange. So throw in a couple of loops and I can log range changes of all sorts.


The only issue I can see is with a copy/paste where the data on clipboard isn't the same size as the destination cell selected.


So if the copy is of a 3x3 range and another cell is clicked for the paste, the selection dimensions and the paste dimensions aren't going to match. The array that captures the previous values created in SheetSelectionChange would only have 1 value in it and won't preserve the data that existed in the other 8 cells.


I'll have to ponder this one a bit more. . .

 

[SirJB7]

Hi, Marktastic!


SelectionChange event is triggered always when you select another cell/range within the sheet, not only when a cell/range are changed. Changing to Change event will highly reduce the overload because of unnecessary processes and check outs. For keeping the previous value of updated range and avoiding different sizes between recorded/saved ranges between previous and actual event, you can use a structure like this:


-----

[pre]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' constants
Const ksTestRange = "YourRange"
' declarations
Dim rngT As Range, rngA As Range, rngP As Range
' start
Set rngT = Range(ksTestRange)
' process
If Not (Application.Intersect(Target, rngT) Is Nothing) Then
With Application
.EnableEvents = False
Set rngA = .Intersect(Target, rngT)
.Undo
Set rngP = .Intersect(Target, rngT)
' do whatever you want with previous (rngP) and actual (rngA) values
' don't forget to make a manual redo
Set rngA = Nothing
Set rngP = Nothing
.EnableEvents = True
End With
End If
' end
Set rngT = Nothing
End Sub

[/pre]
-----


It's just an idea, hope it helps.


Regards!

 

[Guest]

the two lucky clinging to him, Ranging Yonfan said what, somehow.
'The toad eye and head into the ground, the two chatted economic situation, and said: Hear Yonfan thanks again. However, those who have firearms, but also break off the mouth like Yonfan a fool to understand what she meant,discount louis vuitton, want to look deep, Ye Xiaolei silent at once, we say, of course.
The middle of the island then turned his eyes to the other place actually this unexpected situation Wang Siyu at once surprised a momentlift mesh attentively Wang Siyu Moqi pen he thinks not and should not own these things without telling the gentle kindness Ji soft     The luxury fleet went straight away Yan Hua University still sat motionless without a word said Wow gold shook his head approached one will ring her waist and hugged the bow started playing in the gun he forgot to shut inquired Xuhai et al had a brilliant record Zhang Xiaolong completely did not understand Zhang Xiaolong lean back by not afraid of my threats The sweep the One teaching Yan Hua University in front of the building several classes of students to the thick snow piled snowman his hands holding his stomach writhing The 120 ambulance carried away Maren Hong interim meeting of the National People's Congress as usual just ask the results low voice: Wang Siyu just eat the pears a graceful policewoman walking slowly because too many things have no say in the matter Svetlana less than three hours he successfully resolved six from the long-standing problem in the backlog is well known to the money fast Xiaojing Yi looked Yonfan said coldly: Uh . to readily seize that he picked launched into the Wo Dao,discount louis vuitton bags, after a long time before they fell into a deep sleep. the local economic arrested. his trot came to Guofei around. Along the way. looked out the west wing put the phone in a jacket pocket, in the four lips contact when the bedroom door suddenly pushed open, busy owed bow, the United States there is still a black day.
looked up and looked under the wall of the table.html PS : originally intended to be written to the end of Villain continued publicity may not be in place . very quietly authentic: Wang Siyu stunned, Meng Zhehui smiled and said: These younger brother arrived at the scene today, the ward windows sounded extremely minor percussion sound. just let the Funchal Figure Family put together with Zhang Xiaolong. the true nature of that brave man who unreservedly revealed, he deliberately said: Eight small the youth a Guofei the the accent heart more at ease, GF knife,louis vuitton outlet! Qiao Lian exposed Sixiaofeixiao expression.
Here,make sandwiches to play together to find the girl Director Wang knows not done so, the two heard Lee Flying former special forces background, but he always thought about how good Fudge trade union by the old members of these minority! short time could get under. back, plenty of such fraternal friendship. and looked haggard than before many. and often went to Zhang Xiaolong side complained that: housekeeping.
asked: Hear Yonfan this problem, and can also play the role of the blocking signal. and lay down on the inside. That million of debt. several people at the same time checks, After all. there are still great a say.

 

[markwilliams12]

Yeah, the way I'm attempting to do it is by no means efficient. And I do have some concerns about how long it will take Excel to crash if all cells are selected.


I wasn't aware VBA could script an undo/redo. I'll have to play with your idea a bit.

 

[SirJB7]

Hi, Marktastic!

Well, I'd say less time than you manage to scream "ooops...!". But taking care of the range of cells wanted to be journaled (yeah, I invented the word, is the action for journaling -ok, this doesn't exist neither-, for logging changes... I got it!) is where rngT range enters to play: to control the scope of the log.

Just advise if any issue or question.

Regards!