Value of Cell that is selected to Copy or Cut

[GCRDelphi]

I wasnt to know how to get the value when SelectionChange fires and
CutCopyMode is Cut of the cell that is going to be cut and the cell it self.
For example:

(the following is not code)
Selects C5 = 10
Cut
Selects D10
[Selection Change Fires]
If CutCopyMode = xlCut Then
If "Copied Cell Value" < 10 Then CutCopyMode = False
..
..
..

How can I get "Copied Cel Value"?

Thank you for your help.

 

[Mike H]

Hi,

You mention the selection change event so I guess your using that and in
that case

Target.value

will give the value of the cell that called the event

Mike

 

[GCRDelphi]

Not Exactly what I was looking for. Target.Value gives me the Value of the
selected Cell, what I want to find out is what is the Value of the Cell that
is being Cut. For example:

I Select C5, then "cut", then Select D10, I want to find out what value has
C5 to make sure it can be placed is D10.

Maybe this explains more what I need.

Thank you anyway for your input.

 

[Mike H]

Any of these.

Activecell.value

range("C5").value

Cells(5, 3).Value

and the last one

Cells(5, "C").Value

Mike

 

[GCRDelphi]

Sorry, but ActiveCell.Value gives me the Value of the Cell being selected not
the cell that I had intially used "cut" on and the others are for a Specific
Cell not any Cell I cut from. Let me repeat:

Select Cell "X"
Ctrl+X
Select Cell "Y"
Find out what the value of Cell "X" is to verify it can be placed in Cell "Y"

Target.Value = Value of Cell "Y"
ActiveCell.Value = Value of Cell "Y"

I need Value of Cell "X"

Thank you for your input.

 

[Mike H]

I'm now confused, where does CTRL+X come into it your posting in programming!!

Select Cell "X"
Ctrl+X
Select Cell "Y"

The 3 lines of your desciption above, when you select cell "X" and copy it,
it's the active cell and therefore

activecell.value works

There's no need to do any selecting at all, unless your using random numbers
to pick your range to copy you must know where it is and can test the value
with any of the methods I gave you.

Mike

Mike

 

[JLGWhiz]

After you have cut a selection and until you paste it somewhere, it only
exist on the Clipboard and I don't know that there is a facility to return
that value using VBA. At least not in the sense that is being described.

 

[JLGWhiz]

I think you would probably have to do that type of check (compare to
destination)before you cut the value from it's original location by perhaps
using an If...then statement. i.e. If destination range <> source range then
forget it else cut and paste.

 

[Lars-Åke Aspelin]

I think that the OP wants to allow the user to Cut and Paste data on
the sheet, but with some restrictions.
Before pasting a data that is in the Cut-Paste process to a specific
cell, a check should be done on the value that is about to be pasted.
The check may be different for different Targets.
The problem is to find the data that will be Pasted so that the check
could be performed and possibly inhibit the Paste operation.

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

I wasnt to know how to get the value when SelectionChange fires and
CutCopyMode is Cut of the cell that is going to be cut and the cell it self.
For example:

(the following is not code)
Selects C5 = 10
Cut
Selects D10
[Selection Change Fires]
If CutCopyMode = xlCut Then
If "Copied Cell Value" < 10 Then CutCopyMode = False
.
.
.

How can I get "Copied Cel Value"?

Thank you for your help.

Maybe you can Paste value to another, helper Cell, first.
Use a cell, possibly hidden, that is not used for anything else.
Then perform the check and if the Pasting should be allowed then you
can Cut the value again from the helper Cell, thereby allowing the
user to Paste it to the selected target.

Hope this helps / Lars-Åke

 

[Chip Pearson]

Try the following. You'll need to set a reference (in VBA, Tools menu,
References item) to the "Microsoft Forms 2.0 Object Library" in order
to use the DataObject.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim DataObj As New MSForms.DataObject
Dim V As Variant
If Application.CutCopyMode = xlCut Then
DataObj.GetFromClipboard
V = DataObj.GetText
If IsNumeric(V) = True Then
If V < 10 Then
Application.CutCopyMode = False
End If
End If
End If
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[GCRDelphi]

Thank You Mr. Pearson!

That did the trick.

You Truly are a MVP!

Thank you for your help.