Excel - Detecting if a copied cell

[StrikeEagleII]

I'm trying to create a shortcut in Excel, using the Selection.PasteSpecial method ( I want to copy only the text in a cell, not the cell's formatting.) It works great when a cell has actually been copied. If no cell is copied, of course an error is returned. Is there a way to detect whether any cell has been copied? I tried the Selection method, and if a cell has been copied, the Selection method returns the copied cell's contents. If nothing's copied, though, it returns whatever cell was last clicked on. I'm sure it's a pretty simple fix, I'm just not familiar enough with VBA to know all the different properties and methods. Thanks

 

[SkipVought]

Hi,

Check the CutCopyMode Property.

Skip,

_[red][/red]

 

[StrikeEagleII]

That works--thanks! Is there a way to find the address or range of the cells that are copied (have the moving border around them?) When I select the cell I want to paste them into, activecell.address and selection.address both return the cell I just selected to paste the copied cells into. Thanks!

Jason

 

[SkipVought]

In each Worksheet Object there are Events. The Worksheet_SelectionChange event PRIOR to the copy could be used to have captured the address of the Target.

Right click the sheet tab and select View Code

Above the Sheet Code Window, are 2 drop-down controls. The left one is the Object selector; the right is the Procedure selector.

Select Worksheet & SelectionChange. Store the Target.Address in a Public variable or in a sheet.

Skip,

_[red][/red]

 

[combo]

I haven't tested all cases, but this workaround returns address when runs via shortcut key:

Dim picTemp As Excel.Picture
Dim sAddress As String
Set picTemp = ActiveSheet.Pictures.Paste(Link:=True)
sAddress = picTemp.Formula
picTemp.Delete
Range(sAddress).Copy
MsgBox sAddress

combo

 

[StrikeEagleII]

Sweet--that'll work! Thanks for your help!

Jason