Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |