VBA help

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,476
Office Version
  1. 2016
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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,476
Office Version
  1. 2016
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
 

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,476
Office Version
  1. 2016
Platform
  1. Windows
Many thanks - perfect !! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,885
Members
413,947
Latest member
gizmolucy

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