Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default How to run this macro

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default How to run this macro

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.

" wrote:

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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default How to run this macro

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

On 19 Mar 2007 13:32:46 -0700, wrote:

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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default How to run this macro

"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.

"Gord Dibben" wrote:

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

On 19 Mar 2007 13:32:46 -0700, wrote:

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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default How to run this macro

On 20 mar, 01:36, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
"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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default How to run this macro

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

On 20 Mar 2007 01:48:51 -0700, wrote:

On 20 mar, 01:36, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
"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


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default How to run this macro

On 20 mar, 18:47, Gord Dibben <gorddibbATshawDOTca wrote:
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

On 20 Mar 2007 01:48:51 -0700, wrote:

On 20 mar, 01:36, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
"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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 1 February 5th 07 10:31 PM
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 3 February 5th 07 09:22 PM
using a cell value to control a counter inside a macro and displaying macro value ocset Excel Worksheet Functions 1 September 10th 06 05:32 AM
Macro needed to Paste Values and prevent Macro operation thunderfoot Excel Discussion (Misc queries) 1 June 11th 05 12:44 AM
Macro needed to Paste Values and prevent Macro operation thunderfoot Excel Discussion (Misc queries) 0 June 10th 05 03:38 PM


All times are GMT +1. The time now is 08:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"