How to run this macro

[lassang01]

Hello,

would anybody please tell me steps how to run this macro:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
On Error Resume Next
If Target.Value<> "" Then
Target.Value.Copy
End If
Cancel = True
End Sub

and how does it work?

thanks

 

[Guest]

That code needs to go into a worksheet's code module. Easy way to get there
is to choose the sheet and then Right-click on the sheet's name tab and
choose [View Code] from the list that appears. Copy the code and paste it
into the module that you see. Close the VB Editor.

Type something into a cell on the sheet and then double-click it. You won't
see much happen other than perhaps the cell changing to indicate it is being
copied. Click another cell and use [ctrl]+[v] or Edit | Paste and the
contents of the other cell will be pasted into it.

What happens is that when you double-click a cell in a worksheet an 'event'
is triggered (the double-click event). By having code in the worksheets
_BeforeDoubleClick event handler, Excel says "before I do what I would
normally do with a double-click, I should do whatever is in this code
segment".

In the routine 'Target' is the cell or range of cells that were acted on.
Think of it as a mirror image of the cell itself.
On Error Resume Next says that if something I'm trying to do here can't be
done, ignore my mistake and just keep on trying and running the code.
If there is something in the cell, it is copied to the clipboard, if there
isn't any visible text of some type in the cell, nothing gets copied to the
clipboard.
Finally, the Cancel=True statement says "oops, he really didn't mean to
actually double-click the cell, he just wanted a slick way of copying to the
clipboard, so don't do what you normally would have done when a cell is
double-clicked (which is to go into edit mode in the cell).

By the way, because the copy command is given as Target.Value.Copy when you
paste the information it will be much the same as if you'd done a copy from
the keyboard and then used Edit | Paste Special with [Values] chosen as the
option.

And that's how it works.

 

[Gord Dibben]

This is worksheet event code which runs when any cell is double-clicked.

As written it does nothing. Copying a value without doing something with it
throws an error in this code.

If you rem out the On Error Resume Next you would see it crashes on
Target.Value.Copy

Try this version so's you can see how the event will work. See how the
double-clicked cell value is copied three columns to the right on same row.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
On Error Resume Next
If Target.Value <> "" Then
Target.Copy Destination:=Target.Offset(0, 3)
End If
Cancel = True
End Sub

Right-click on your sheet tab and "View Code" Copy/paste the above into that
sheet module.

Here's another set of code that brings a value into the double-clicked cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
On Error Resume Next
If Target.Value <> "" Then
Target.Value = Target.Offset(0, 4).Value
End If
Cancel = True
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

"Copying a value without doing something with it
throws an error in this code."

Darn, I didn't think about that aspect of it. Good point.

 

[lassang01]

"Copying a value without doing something with it
throws an error in this code."

Thanks very much for your clarification.

as was stated i have tried the macro but nothing has been happened
with this macro except the double click function.

would you help me please to get another code that can copy a value
from cell into clipboard either by one or double click.

Thanks in advance.

Lassaad

 

[Gord Dibben]

To copy to the clipboard........

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
On Error Resume Next
If Target.Value <> "" Then
Target.Copy
End If
Cancel = True
End Sub


Gord

 

[lassang01]

To copy to the clipboard........

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
On Error Resume Next
If Target.Value <> "" Then
Target.Copy
End If
Cancel = True
End Sub

Gord