excel - 从 VBA Excel 中删除数据

Question

所以目前我正在研究 VBA 表单（产品大师），并遇到了这个问题。所以基本上，我的数据有 3 个按钮，即添加、更新和删除。完成添加和更新按钮。只是不知道如何从用户表单中的列表框中删除我的数据，并且此用户表单已连接到工作表。

'''

Option Compare Text
Private Sub btn_ProductMaster_Add_Click()



If Me.txt_Product_Name.Value = "" Then
MsgBox "Please Enter Product Name", vbCritical
    Exit Sub
End If

If IsNumeric(Me.txt_Purchase_Price) = False Then

MsgBox "Please Enter Product Price", vbCritical
    Exit Sub
End If

If Me.txt_Product_colour.Value = "" Then
MsgBox "Please Enter Product Colour", vbCritical
    Exit Sub
End If

Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Product_Master")

Dim lr As Integer
lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))

Me.txt_id = StrConv(Me.txt_Product_Name.Value, vbProperCase) & "_" & StrConv(Me.txt_Product_colour.Value, vbProperCase)

If Application.WorksheetFunction.CountIf(sh.Range("B:B"), Me.txt_id.Value) > 0 Then
MsgBox "This Product is already Available in product master", vbCritical
    Exit Sub
End If

sh.Range("A" & lr + 1).Value = lr
sh.Range("B" & lr + 1).Value = Me.txt_id.Value
sh.Range("C" & lr + 1).Value = StrConv(Me.txt_Product_Name.Value, vbProperCase)
sh.Range("D" & lr + 1).Value = StrConv(Me.txt_Product_colour.Value, vbProperCase)
sh.Range("E" & lr + 1).Value = Me.txt_Purchase_Price.Value

Me.txt_id.Value = ""
Me.txt_Product_Name.Value = ""
Me.txt_Product_colour.Value = ""
Me.txt_Purchase_Price.Value = ""

MsgBox "Product has been added", vbInformation
Call show_data



End Sub


Sub show_data()

Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Product_Master")

Dim lr As Integer
lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))
 
 If lr = 1 Then lr = 2
 
 
With Me.list_ProductMaster
    
    .ColumnCount = 5
    .ColumnHeads = True
    .ColumnWidths = "40,110,110,80,80"
    .RowSource = "Product_Master!A2:E" & lr
    
    
End With

End Sub


Private Sub btn_ProductMaster_Dlt_Click()

    list_ProductMaster.SetFocus
    
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Product_Master")
    Dim lr As Integer
    
    
    lr = Me.txt_srno.Value
    MsgBox lr
    
      
        
        sh.Cells(lr + 1, "A").EntireRow.Delete
        
        Me.txt_srno.Value = ""
        Me.txt_id.Value = ""
        Me.txt_Product_Name.Value = ""
        Me.txt_Product_colour.Value = ""
        Me.txt_Purchase_Price.Value = ""
    
    
    MsgBox "Product has been Deleted", vbInformation
    
    
    Call show_data

End Sub


Private Sub btn_ProductMaster_Extract_Click()

Dim nwb As Workbook
Set nwb = Workbooks.Add

ThisWorkbook.Sheets("Product_Master").UsedRange.Copy nwb.Sheets(1).Range("a1")


End Sub

Private Sub btn_ProductMaster_Updt_Click()

If Me.txt_Product_Name.Value = "" Then
    MsgBox "Please Enter Product Name", vbCritical
    Exit Sub
End If

If IsNumeric(Me.txt_Purchase_Price) = False Then
    MsgBox "Please Enter Product Price", vbCritical
    Exit Sub
End If

If Me.txt_Product_colour.Value = "" Then
    MsgBox "Please Enter Product Colour", vbCritical
    Exit Sub
End If

Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Product_Master")


Dim lr As Integer
lr = Me.txt_srno.Value

Me.txt_id = StrConv(Me.txt_Product_Name.Value, vbProperCase) & "_" & StrConv(Me.txt_Product_colour.Value, vbProperCase)

sh.Range("A" & lr + 1).Value = lr
sh.Range("B" & lr + 1).Value = Me.txt_id.Value
sh.Range("C" & lr + 1).Value = StrConv(Me.txt_Product_Name.Value, vbProperCase)
sh.Range("D" & lr + 1).Value = StrConv(Me.txt_Product_colour.Value, vbProperCase)
sh.Range("E" & lr + 1).Value = Me.txt_Purchase_Price.Value

Me.txt_srno.Value = ""
Me.txt_id.Value = ""
Me.txt_Product_Name.Value = ""
Me.txt_Product_colour.Value = ""
Me.txt_Purchase_Price.Value = ""

MsgBox "Product has been Updated", vbInformation
Call show_data



End Sub


Private Sub list_ProductMaster_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    Me.txt_srno.Value = Me.list_ProductMaster.List(Me.list_ProductMaster.ListIndex, 0)
    Me.txt_id.Value = Me.list_ProductMaster.List(Me.list_ProductMaster.ListIndex, 1)
    Me.txt_Product_Name.Value = Me.list_ProductMaster.List(Me.list_ProductMaster.ListIndex, 2)
    Me.txt_Product_colour.Value = Me.list_ProductMaster.List(Me.list_ProductMaster.ListIndex, 3)
    Me.txt_Purchase_Price.Value = Me.list_ProductMaster.List(Me.list_ProductMaster.ListIndex, 4)

End Sub


Private Sub UserForm_Activate()
    
    Call show_data
    
End Sub

'''

标签： excelvbalistbox