时间戳

首页 > 解决方案 > 交互式文本框选择取消时如何取消案例

excel - 交互式文本框选择取消时如何取消案例

问题描述

所以我正在编写一个案例函数，当事件（7 - Engaged）发生时，会弹出一个交互式文本框，要求用户确认此操作。如果他们选择确定，则数据将移动到另一个电子表格。

这一切都很好，但可能需要修改以整理它。

无论如何，当用户选择取消时就会出现问题。数据行被删除，而不是仅仅离开函数。

我相信这个问题是最后几行删除了任何 7 参与的内容，但是如果用户取消，我还没有编写一段代码来将值降低到 6。

谁能给我一些提示？

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) '可能在此代码运行时禁用事件（并在退出前重新启用）'以防止递归。

' The three range rows are to move sepearate sections of data from pipeline into isolated blocks in tank.

 If Source.Column <> 9 Then Exit Sub ' 9 = I
 If Source.Cells.Count > 1 Then Exit Sub ' Check this first before making comparison on next line
 If Source.Value <> "7 - engaged" Then Exit Sub

If MsgBox("Client status selected as engaged. Confirm to post to tank.", vbOKCancel) = vbOK Then
    With ThisWorkbook.Worksheets("Tank") 'Produces an interactive dialoge box prompting the user to confirm they wish ti import to tank
    'The code only fires if they confirm - if not, the line will remain in Pipeline.
        Dim rowToPasteTo As Long
        rowToPasteTo = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

        .Range("A" & rowToPasteTo & ":" & "D" & rowToPasteTo).Value = Sh.Range("A" & Source.Row & ":" & "M" & Source.Row).Value
        .Range("G" & rowToPasteTo & ":" & "H" & rowToPasteTo).Value = Sh.Range("E" & Source.Row & ":" & "F" & Source.Row).Value
        .Range("S" & rowToPasteTo & ":" & "U" & rowToPasteTo).Value = Sh.Range("K" & Source.Row & ":" & "M" & Source.Row).Value



    End With

 End If

If Source.Column = 9 And Source.Value = "7 - engaged" Then
Source.EntireRow.Delete

' The above line deleted the row from pipeline once it has been imported in Tank

End If


End Sub

标签： excelvba

解决方案

我现在添加了这段代码来解决这个问题。

 If MsgBox("Client status selected as engaged. Confirm to post to tank.", vbOKCancel) = vbCancel Then
Source.Value = "6 - KYC in progress" ' If cancel is selected the value goes back to Case 6 and the line is kept.


End If

推荐阅读