Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cut Row and paste in a different Sheet when Doing double click.

Posted on 2009-03-29
4
Medium Priority
?
336 Views
Last Modified: 2013-11-10
Hi,

I have the following code in sheet "ENTREGAS" that works as a Check box.

What I need to add to the code is the action to cut the ROW where I´m doing the doubleclick and paste it to Sheet "ENTREGADOS".

CODE:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     Dim lngRow As Long
     'Limit Target count to 1
    If Target.Count > 1 Then Exit Sub
     'Isolate Target to a specific range
    If Intersect(Target, Range("tildes")) Is Nothing Then Exit Sub
    lngRow = Target.Row
     'set Target font tp "marlett"
    Target.Font.Name = "marlett"
     'Check value of target
    If Target.Value <> "a" Then
        Target.Value = "a" 'Sets target Value = "a"
        ' check it's column B
     
   
        Cancel = True
        Exit Sub
    End If
    If Target.Value = "a" Then
        Target.ClearContents 'Sets Target Value = ""
         With Sheets("ENTREGAS")
            .Cells(Target.Row, Target.Column + 1).Value = ""
         
        End With
       
        Cancel = True
        Exit Sub
    End If
End Sub


Could you help me please ?

Regards,
Roberto.
0
Comment
Question by:Pabilio
  • 2
  • 2
4 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24015012
something like this - see sample file
Cheers
Dave

Option Explicit
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim ws As Worksheet, rng1 As Range
    Cancel = True
    Set ws = Sheets("ENTREGADOS")
    Set rng1 = ws.Cells(Rows.Count, "A").End(xlUp)
    If rng1.Row <> vbNullString Then Set rng1 = rng1.Offset(1, 0)
    With Target
        .EntireRow.Copy rng1
        .EntireRow.Delete shift:=xlUp
    End With
End Sub

Open in new window

cutnpaste.xls
0
 
LVL 5

Author Comment

by:Pabilio
ID: 24015078
hi Dave,

The code gave me error 13
And highlighted line 8:  If rng1.Row <> vbNullString

I forgot to say in my question that the code must run when doing double click only in any cell in column G (Range tildes is from G2 to G2000)

Thank you for your help.

Roberto.
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 2000 total points
ID: 24015108
Take 2 - with Column G restriction added
Cheers
Dave
 
 


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim ws As Worksheet, rng1 As Range
    Cancel = True
    If Target.Column <> 7 Then Exit Sub
    Set ws = Sheets("ENTREGADOS")
    Set rng1 = ws.Cells(Rows.Count, "A").End(xlUp)
    If rng1.Value <> vbNullString Then Set rng1 = rng1.Offset(1, 0)
    With Target
        .EntireRow.Copy rng1
        .EntireRow.Delete shift:=xlUp
    End With
End Sub

Open in new window

0
 
LVL 5

Author Closing Comment

by:Pabilio
ID: 31564113
Take 2 is the one....
Just perfect !!!
Thank you Dave.
P.S.: Dave if you have time, please have a look at this question:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_24254306.html
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question