VBA help

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,575
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have this code that was kindly given by a post on here several months back and I now wish to change it slightly, the user double clicks on a text string and then after a popup checking for varification the whole line of data is removed.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
rspn = MsgBox("You have chosen to delete " & Cells(Target.Row, 2) & " " & Cells(Target.Row, 3) & Chr(10) & "Is that correct?", vbYesNo)
If rspn = vbYes Then Target.EntireRow.Delete
End Sub

the change to the code I would like is this:_

before the whole line is removed can it first be copied into next available row in "database" sheet. Once copied then the selected line (ie the original code) can be removed.

I'm guessing that the
Code:
if rspn=vbYes Then Target.EntireRow.Delete
would be changed....but not sure how

many thanks in advance
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
rspn = MsgBox("You have chosen to delete " & Cells(Target.Row, 2) & " " & Cells(Target.Row, 3) & Chr(10) & "Is that correct?", vbYesNo)
If rspn = vbYes Then
    Target.EntireRow.Copy Destination:=Sheets("Database").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Target.EntireRow.Delete
End If
End Sub
 

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,575
Office Version
  1. 365
Platform
  1. Windows
Hi

Thanks for help with code, however the copied text that is going into the database sheet is replacing the last row...where as it should be placed into next available row so that a database is kinda created.

Just one other additional request...

can the copied text also be pasted into the database sheet with the font at size 8 and black in colour??

thanks again
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I'm guessing that there is nothing in column A. Try this

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LR As Long
Cancel = True
rspn = MsgBox("You have chosen to delete " & Cells(Target.Row, 2) & " " & Cells(Target.Row, 3) & Chr(10) & "Is that correct?", vbYesNo)
If rspn = vbYes Then
    LR = Sheets("Database").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Target.EntireRow.Copy Destination:=Sheets("Database").Range("A" & LR + 1)
    With Sheets("Database").Rows(LR + 1)
        .Font.Color = xlAutomatic
        .Font.Size = 8
    End With
    Target.EntireRow.Delete
End If
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,893
Messages
5,834,274
Members
430,273
Latest member
Windrunner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top