我的第一個 Sub 使用我在用戶表單中輸入的值來搜索另一張表上的表，并使用該表中當前輸入的值更新用戶表單。
我有第二個 Sub 會將更新的值寫回同一張表。我有一個復選框，當相關專案完成并且我們不再需要行中的資料并希望將其洗掉時，用戶會檢查該復選框。
當檢查此后，用戶單擊更新時，子是從紙張中洗掉該行。
主作業表只是包含實際資料的作業表的副本。該表從 Zapier 接收更新。我有隱藏行和列的宏，當他們嘗試添加資料時，這會與連接的應用程式混淆，這是第二張作業表的原因。
我今天花了一整天的時間試圖弄清楚如何洗掉用戶表單中搜索功能選擇的行。子在更新行時執行相同的搜索，所以我想如果我添加一個 IF 陳述句來更新或洗掉取決于復選框的狀態。
這是我的代碼：
Private Sub UpdateRecord()
Dim RecordRow As Long
Dim RecordRange As Range
Dim Answer As VbMsgBoxResult
Dim DeleteRow As Long
Dim DeleteRange As Range
Dim ws As String
' Find the row in the table that the record is in
RecordRow = Application.Match(CLng(TextBoxWO.Value), Range("JobSheetData[W/O]"), 0)
' Set RecordRange to the first cell in the found record
Set RecordRange = Range("JobSheetData").Cells(1, 1).Offset(RecordRow - 1, 0)
If CheckBoxDelete = "True" Then
'---------------------True = Delete-------------------
' Find the row in the table that the record is in
DeleteRow = Application.Match(CLng(TextBoxWO.Value), Range("JobSheetData[W/O]"), 0)
' Set RecordRange to the first cell in the found record
Set DeleteRange = Range("JobSheetData").Cells(1, 1).Offset(RecordRow - 1, 0)
Answer = MsgBox("Are you sure you want to PERMANENTLY DELETE this job from the job list?" & vbCrLf & vbCrLf & "This action cannot be undone!", vbOKCancel vbDefaultButton2, "Confirm removal of job from list")
If Answer = vbYes Then
ActiveWorkbook.Worksheets("Job List Import").ListObjects("JobSheetData").ListRows(DeleteRange).Delete
End If
'---------------------False = Update-------------------
Else
RecordRange(1, 1).Offset(0, 5).Value = TextBoxHold.Value
RecordRange(1, 1).Offset(0, 7).Value = TextBoxDays.Value
RecordRange(1, 1).Offset(0, 9).Value = CheckBoxLocate.Value
RecordRange(1, 1).Offset(0, 13).Value = TextBoxFirst.Value
RecordRange(1, 1).Offset(0, 14).Value = TextBoxOveride.Value
RecordRange(1, 1).Offset(0, 15).Value = CheckBoxBell.Value
RecordRange(1, 1).Offset(0, 16).Value = CheckBoxGas.Value
RecordRange(1, 1).Offset(0, 17).Value = CheckBoxHydro.Value
RecordRange(1, 1).Offset(0, 18).Value = CheckBoxWater.Value
RecordRange(1, 1).Offset(0, 19).Value = CheckBoxCable.Value
RecordRange(1, 1).Offset(0, 20).Value = CheckBoxOther1.Value
RecordRange(1, 1).Offset(0, 21).Value = CheckBoxOther2.Value
RecordRange(1, 1).Offset(0, 22).Value = CheckBoxOther3.Value
End If
End Sub
我也嘗試合并此代碼，但也無法使其正常作業：
Dim Answer As VbMsgBoxResult
Answer = MsgBox("Are you sure you want to PERMANENTLY DELETE this job from the job list?" & vbCrLf & vbCrLf & "This action cannot be undone!", vbOKCancel vbDefaultButton2, "Confirm removal of job from list")
If Answer = vbYes Then
If JobSheetInputForm.ListRows.Count < 1 Then Exit Sub
With ActiveSheet.ListObjects("JobSheet")
JobSheetInputForm.ListRows(CurrentRow).Delete
If JobSheetInputForm.ListRows.Count > 0 Then
If CurrentRow > JobSheetInputForm.ListRows.Count Then
CurrentRow = JobSheetInputForm.ListRows.Count
End If
JobSheetInputForm.ListRows(CurrentRow).Range.Select
Else
CurrentRow = 0
End If
End With
End If
我在這一點上被打敗了，需要你的幫助！謝謝！
uj5u.com熱心網友回復：
像這樣的東西：
Sub Test()
Dim rngSrch As Range, lo As ListObject, DeleteRow, v
Set lo = ActiveWorkbook.Worksheets("Job List Import").ListObjects("JobSheetData")
Set rngSrch = lo.ListColumns("W/O").DataBodyRange 'range to search in
v = CLng(TextBoxWO.Value) 'value to search
DeleteRow = Application.Match(v, rngSrch, 0) 'try to match a row
If Not IsError(DeleteRow) Then 'got match?
If MsgBox("Are you sure you want to PERMANENTLY DELETE this job from the job list?" & _
vbCrLf & vbCrLf & "This action cannot be undone!", _
vbOKCancel vbDefaultButton2, "Confirm removal of job from list") = vbOK Then
lo.ListRows(DeleteRow).Delete
End If
Else
MsgBox "Value not found!" 'no match
End If
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/449910.html
下一篇：從表中洗掉特定行