Count number of cells in worksheet_change

[YasserKhalil]

Hello
I know my problem is strange.

I want to write a code in Worksheet_Change that count the number of cells to any selection as soon as I copy these cells..

In other words : say I select the range ("A1:B5") then right click and copy.

At this moment I want a message telling me the number of cells selected and copied.

Is that possible?

[Bob Phillips]

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Cells.Count
End Sub
[/vba]

[YasserKhalil]

Thanks for your reply
It works fine when entering data in a cell and when use the Paste command.
I don't want that action when entering data.
I just want to do that after copying a range..

[Bob Phillips]

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CutCopyMode Then MsgBox Target.Cells.Count
Application.CutCopyMode = False
End Sub[/VBA]

[YasserKhalil]

I appreciate your help Mr. xld But it doesn't work
I want the message to appear immediately after copying and don't cancel the process of copying

[YasserKhalil]

Is it possible to find out the number of cells after copying or not?

[mikerickson]

You may not need VBA for this.

I'm using Excel 2011. When I select a range, the number of rows and columns selected shows in the Name box (to the left of the Formula box)

For example
Select C3:E6 and (as long as the mouse is depressed) "4R X 3C" shows in the name box.

If you can multiply the numbers you are likely to encounter in your head, there is no need for a count of cells selected message box.

[YasserKhalil]

thanks for the information you provided.
I still want it by code

[mikerickson]

Perhaps something like this, in the sheet's code module will work for you.
If the user selects a range and then copies (or cuts) that range, when the next range is selected, a "row by column" value will be shown in the status bar.
[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static rngLast As Range

If rngLast Is Nothing Then
Set rngLast = Target
Else
With Application
If .CutCopyMode Then
.StatusBar = rngLast.Rows.Count & "R X " & rngLast.Columns.Count & "C copied"
Else
Application.StatusBar = False
Set rngLast = Target
End If
End With
End If
End Sub[/VBA]
Excel has no OnCopy event, so the message won't show when the user presses copy, but when the next range is selected.

One other bug with this approach is that if the user copies one range, selects another range (data from one range is shown), and doesn't paste or Esc, but copies again, the data from the first range will be continue to be shown.

[YasserKhalil]

thank you very much for your help Mr. Mikerickson