Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
specifying the column for double click
Thanks to the generous contriutors here, I have the following code which
copies data from one sheet to another: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True ' prevent double-click from causing Edit Mode in cell Application.Run "CopyProcedure" End Sub Sub CopyProcedure() ActiveCell.Range("A1:E1").Select Selection.Copy Sheets("Report").Activate mlastrow = Cells(Rows.Count, "A").End(xlUp).Row Cells(mlastrow + 1, "A").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub It works great except for one little thing: because it's supposed to copy data from columns A to E of the same row when you double-click any cell in coumn A, it also will copy the corresponding five adjacent cells no matter where you double-click on the sheet. How can I limit the code so that only when what is double-clicked is in column A will the maco activate? Thanx for any help anyone can provide on this. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
specifying the column for double click
add this
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) IF target.Column < 6 then Cancel = True ' prevent double-click from causing Edit Mode in cell Application.Run "CopyProcedure" End IF End Sub "Rhino V" wrote: Thanks to the generous contriutors here, I have the following code which copies data from one sheet to another: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True ' prevent double-click from causing Edit Mode in cell Application.Run "CopyProcedure" End Sub Sub CopyProcedure() ActiveCell.Range("A1:E1").Select Selection.Copy Sheets("Report").Activate mlastrow = Cells(Rows.Count, "A").End(xlUp).Row Cells(mlastrow + 1, "A").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub It works great except for one little thing: because it's supposed to copy data from columns A to E of the same row when you double-click any cell in coumn A, it also will copy the corresponding five adjacent cells no matter where you double-click on the sheet. How can I limit the code so that only when what is double-clicked is in column A will the maco activate? Thanx for any help anyone can provide on this. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
specifying the column for double click
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean) If Target.Column = 1 then Cancel = True ' prevent double-click from causing Edit Mode in cell Application.Run "CopyProcedure" End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Rhino V" wrote: Thanks to the generous contriutors here, I have the following code which copies data from one sheet to another: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True ' prevent double-click from causing Edit Mode in cell Application.Run "CopyProcedure" End Sub Sub CopyProcedure() ActiveCell.Range("A1:E1").Select Selection.Copy Sheets("Report").Activate mlastrow = Cells(Rows.Count, "A").End(xlUp).Row Cells(mlastrow + 1, "A").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub It works great except for one little thing: because it's supposed to copy data from columns A to E of the same row when you double-click any cell in coumn A, it also will copy the corresponding five adjacent cells no matter where you double-click on the sheet. How can I limit the code so that only when what is double-clicked is in column A will the maco activate? Thanx for any help anyone can provide on this. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
specifying the column for double click
I think the following code will do what you want...
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) If Target.Column = 1 Then Cancel = True ' prevent double-click from causing Edit Mode in cell CopyProcedure Target.Row End If End Sub Sub CopyProcedure(TargetRow As Long) Dim LastRow As Long Range("A" & TargetRow & ":E" & TargetRow).Copy With Sheets("Report") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Cells(LastRow + 1, "A").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End With End Sub Notice I added an argument to your CopyProcedure subroutine to that calling code can pass it the row number to be copied. I also changed how you call the CopyProcedure subroutine from your Application.Run method to the more normal way of calling a subroutine... simply using its name and follow that with any arguments that it might have. Instead of activating the Report sheet, I simply referenced it by including the commands that apply to it with a leading dot inside of a With/EndWith block. I also removed all the select and cell activate you used in favor of the more efficient direct property/method calls. Perhaps this previous posting of mine (a response to another person using Select/Selection type constructions; applies to Activate for a cell as well) will be of some help to you in your future programming... Whenever you see code constructed like this... Range("A1").Select Selection.<whatever you can almost always do this instead... Range("A1").<whatever In your particular case, you have this... Range("C2:C8193").Select 'select cells to export For Each r In Selection.Rows which, using the above concept, can be reduced to this... For Each r In Range("C2:C8193").Rows Notice, all I have done is replace Selection with the range you Select(ed) in the previous statement and eliminate the process of doing any Select(ion)s. Stated another way, the Selection produced from Range(...).Select is a range and, of course, Range(...) is a range... and, in fact, they are the same range, so it doesn't matter which one you use. The added benefit of not selecting ranges first is your active cell does not change. -- Rick (MVP - Excel) "Rhino V" wrote in message ... Thanks to the generous contriutors here, I have the following code which copies data from one sheet to another: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True ' prevent double-click from causing Edit Mode in cell Application.Run "CopyProcedure" End Sub Sub CopyProcedure() ActiveCell.Range("A1:E1").Select Selection.Copy Sheets("Report").Activate mlastrow = Cells(Rows.Count, "A").End(xlUp).Row Cells(mlastrow + 1, "A").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub It works great except for one little thing: because it's supposed to copy data from columns A to E of the same row when you double-click any cell in coumn A, it also will copy the corresponding five adjacent cells no matter where you double-click on the sheet. How can I limit the code so that only when what is double-clicked is in column A will the maco activate? Thanx for any help anyone can provide on this. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
specifying the column for double click
One more...
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) 'this probably isn't necessary, but it doesn't hurt if target.cells.count 1 then exit sub end if if intersect(target, me.range("A:a")) is nothing then exit sub end if 'only cancel if you're in column A Cancel = True ' prevent double-click from causing Edit Mode in cell Call CopyProcedure(mycell:=target) 'why use application.run??? End Sub Sub CopyProcedure(myCell as range) dim DestCell as range with worksheets("report") set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0) end with mycell.resize(1,5).copy destcell.pastespecial Paste:=xlPasteValues, _ Operation:=xlNone, skipBlanks:=False, _ Transpose:=False End Sub ======= You may want to consider being more forgiving. Let them doubleclick on any column in that row. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) 'this probably isn't necessary, but it doesn't hurt if target.cells.count 1 then exit sub end if 'only cancel if you're in column A Cancel = True ' prevent double-click from causing Edit Mode in cell Call CopyProcedure(mycell:=target.entirerow.cells(1)) End Sub (the other procedure doesn't change.) (all untested, uncompiled. Watch for typos.) Rhino V wrote: Thanks to the generous contriutors here, I have the following code which copies data from one sheet to another: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True ' prevent double-click from causing Edit Mode in cell Application.Run "CopyProcedure" End Sub Sub CopyProcedure() ActiveCell.Range("A1:E1").Select Selection.Copy Sheets("Report").Activate mlastrow = Cells(Rows.Count, "A").End(xlUp).Row Cells(mlastrow + 1, "A").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub It works great except for one little thing: because it's supposed to copy data from columns A to E of the same row when you double-click any cell in coumn A, it also will copy the corresponding five adjacent cells no matter where you double-click on the sheet. How can I limit the code so that only when what is double-clicked is in column A will the maco activate? Thanx for any help anyone can provide on this. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
specifying the column for double click
Thank you. That certainly did what I needed it to do.
"Jacob Skaria" wrote: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Column = 1 then Cancel = True ' prevent double-click from causing Edit Mode in cell Application.Run "CopyProcedure" End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Rhino V" wrote: Thanks to the generous contriutors here, I have the following code which copies data from one sheet to another: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True ' prevent double-click from causing Edit Mode in cell Application.Run "CopyProcedure" End Sub Sub CopyProcedure() ActiveCell.Range("A1:E1").Select Selection.Copy Sheets("Report").Activate mlastrow = Cells(Rows.Count, "A").End(xlUp).Row Cells(mlastrow + 1, "A").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub It works great except for one little thing: because it's supposed to copy data from columns A to E of the same row when you double-click any cell in coumn A, it also will copy the corresponding five adjacent cells no matter where you double-click on the sheet. How can I limit the code so that only when what is double-clicked is in column A will the maco activate? Thanx for any help anyone can provide on this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double GET-Request if click on a hyperlink in a column | Excel Discussion (Misc queries) | |||
Zip + 4 format column then must double click to change - why? | Excel Discussion (Misc queries) | |||
Click on graph bar to execute a double-click in a pivot table cell | Charts and Charting in Excel | |||
Q. How do I code a column to insert current date, when I double-click? | Excel Programming | |||
Mouse Over Graph, Capture Information on Click(Double Click) | Excel Programming |