For a table like this:
对于这样的表:
| B | C | D | E | F
---+------+--------+------+--------+------
1 | Name | Number | Fab | Design | Hours
---+------+--------+------+--------+------
2 | AAAA | 10 | 35 | 811 | 505
3 | BBBB | 11 | 541 | 18 | 694
4 | CCCC | 12 | 9622 | 81 | 181
5 | DDDD | 13 | 626 | 848 | 111
6 | EEEE | 14 | 6 | 821 | 6616
7 | FFFF | 15 | 6262 | 862 | 60
8 | GGGG | 16 | 306 | 941 | 6166
9 | HHHH | 17 | 365 | 484 | 6
after deleting a cell in a column B, my code displays a message box asking the user if they're sure before deleting the entire row.
删除B列中的单元格后,我的代码会显示一个消息框,询问用户在删除整行之前是否确定。
How do I get the value of the cell before it was deleted and pass it to another macro that deletes cells in another worksheet based on that value.
如何在删除单元格之前获取单元格的值,并将其传递给另一个宏,该宏根据该值删除另一个工作表中的单元格。
Here is the code:
这是代码:
Private Sub Worksheet_Change(ByVal Target As Range)
' If target column is B, is below the header row AND it's new value is blank then...
If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then
' Clear contents of row
ans = MsgBox("Are you sure you want to Delete......This cannot Be Undone !!!", vbYesNo)
If ans = vbYes Then
'How do I select Column 2, Row active cell and copy the value?
'How do I make the value into variable YYY?
Rows(ActiveCell.Row).EntireRow.Delete
Exit Sub
End If
End If
End Sub
1 个解决方案
#1
0
One way you could get the value that had already been deleted is by utilising the "Undo" function, try this:
一种可以获取已经删除的值的方法是使用“撤消”功能,试试这个:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans As Integer
Dim YYY As String
If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then
ans = MsgBox("Are you sure you want to Delete......This cannot Be Undone !!!", vbYesNo)
If ans = vbYes Then
With Application
.EnableEvents = False ' freeze WorkSheetChange event since you're about to UNDO a change
.Undo ' undo the change
YYY = Target.Value ' assign the deleted-undone value in to YYY
Rows(ActiveCell.Row).EntireRow.Delete ' delete that row
.EnableEvents = True ' re-enable all events
End With
MsgBox YYY ' now you have that value in YYY
Exit Sub
End If
End If
End Sub
#1
0
One way you could get the value that had already been deleted is by utilising the "Undo" function, try this:
一种可以获取已经删除的值的方法是使用“撤消”功能,试试这个:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans As Integer
Dim YYY As String
If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then
ans = MsgBox("Are you sure you want to Delete......This cannot Be Undone !!!", vbYesNo)
If ans = vbYes Then
With Application
.EnableEvents = False ' freeze WorkSheetChange event since you're about to UNDO a change
.Undo ' undo the change
YYY = Target.Value ' assign the deleted-undone value in to YYY
Rows(ActiveCell.Row).EntireRow.Delete ' delete that row
.EnableEvents = True ' re-enable all events
End With
MsgBox YYY ' now you have that value in YYY
Exit Sub
End If
End If
End Sub