Excel Forum

Code: Select all

 LastRow = Sheets("edit and delete").Range("G" & Rows.Count).End(xlUp).Row

Code: Select all

ListBox1.RowSource = "G2:I2000"

Code: Select all

Private Sub CommandButton1_Click() 'Show Data

    ListBox1.ColumnCount = 3
    ListBox1.RowSource = "G2:I2000"

End Sub

Code: Select all

Private Sub ComboBox1_Change() 

    For i = 2 To Application.WorksheetFunction.CountA(Sheets("edit and delete").Range("G:G"))
        If Sheets("edit and delete").Cells(i, 3).Value = Me.ComboBox1.Value Then
        Me.ListBox1.AddItem Cells(i, 1).Value
        Me.ListBox1.AddItem Cells(i, 2).Value
        Me.ListBox1.AddItem Cells(i, 3).Value
    End If
Next i

End Sub

Code: Select all

If Sheets("edit and delete").Cells(i, 3).Value = Me.ComboBox1.Value Then

Code: Select all

            Cells(i, 2) = TextBox1.Text
            Cells(i, 3) = TextBox2.Text

Code: Select all

Private Sub CommandButton1_Click()  'save
    Dim i As Integer

    For i = 2 To Sheets("edit and delete").Range("B" & Rows.Count).End(xlUp).Row
        If Cells(i, 7) = Label4.Caption Then
            Cells(i, 2) = TextBox1.Text
            Cells(i, 3) = TextBox2.Text
        End If
    Next i

    MsgBox "Saved!", vbInformation
    'Unload Me
End Sub

Code: Select all

                Cells(i, 2).Value = ""
                Selection.delete
                Cells(i, 3).Value = ""
                Selection.delete

Code: Select all

Private Sub DeleteSelected_Click() 

    Dim i As Integer
    
    If MsgBox("Are you sure you want to delete the row?", vbYesNo + vbQuestion, "Delete row") = vbYes Then
        For i = 1 To Sheets("edit and delete").Range("B" & Rows.Count).End(xlUp).Row
            If Cells(i, 2) = ListBox1.List(ListBox1.ListIndex) Then
                Cells(i, 2).Select
                Cells(i, 2).Value = ""
                Cells(i, 2).Select
                Cells(i, 2).Value = ""
                Selection.delete
                Cells(i, 3).Value = ""
                Selection.delete
            End If
        Next i
    End If
End Sub

Code: Select all

ListBox1.RowSource = Sheets("edit and delete").Range(Cells(lastlow, 7), Cells(lastlow, 9)).Address

Code: Select all

Private Sub CommandButton1_Click()
Dim lastlow As Long

        lastlow = Sheets("edit and delete").Range("G" & Rows.Count).End(xlUp).Row

        ListBox1.ColumnCount = 3
        ListBox1.RowSource = Sheets("edit and delete").Range(Cells(lastlow, 7), Cells(lastlow, 9)).Address

End Sub 

Code: Select all

Private Sub ComboBox1_Change() 

    For i = 2 To Application.WorksheetFunction.CountA(Sheets("edit and delete").Range("G:G"))
        If Sheets("edit and delete").Cells(i, 3).Value = Me.ComboBox1.Value Then
        Me.ListBox1.AddItem Cells(i, 1).Value
        Me.ListBox1.AddItem Cells(i, 2).Value
        Me.ListBox1.AddItem Cells(i, 3).Value
    End If
Next i

End Sub

Code: Select all

If Sheets("edit and delete").Cells(i, 3).Value = Me.ComboBox1.Value Then

Code: Select all

Dim arr() As Variant, j As Integer, k As Integer
Me.ListBox1.RowSource = ""
Me.ListBox1.ColumnCount = 3
k = Application.CountIf(Sheets("edit and delete").Range("G:G"), Me.ComboBox1.Text)
ReDim arr(0 To k, 2)
For i = 2 To Application.WorksheetFunction.CountA(Sheets("edit and delete").Range("G:G"))
    If Sheets("edit and delete").Cells(i, "I").Value = Me.ComboBox1.Value Then
        arr(j, 0) = Sheets("edit and delete").Cells(i, "G").Value
        arr(j, 1) = Sheets("edit and delete").Cells(i, "H").Value
        arr(j, 2) = Sheets("edit and delete").Cells(i, "I").Value
        j = j + 1
    End If
Next i
Me.ListBox1.List() = arr

Code: Select all

Private Sub CommandButton2_Click()
    UserForm2.Hide
    Unload UserForm2
End Sub

Code: Select all

Private Sub CommandButton4_Click() 'edit selected

On Error GoTo ErrorHandler:

    UserForm2.Label4 = ListBox1.List(ListBox1.ListIndex)
    UserForm2.TextBox1.Text = ListBox1.Column(0, ListBox1.ListIndex)
    UserForm2.TextBox2.Text = ListBox1.Column(1, ListBox1.ListIndex)
    UserForm2.Show

ErrorHandler:

    MsgBox "No Data or Click the info"
    
        Exit Sub
        
   Resume Next

End Sub

Code: Select all

 'Unload UserForm2 

Code: Select all

Private Sub CommandButton2_Click()
    UserForm2.Hide
    'Unload UserForm2
End Sub

Code: Select all

UserForm2.Hide
    Unload UserForm2

Code: Select all

Private Sub CommandButton4_Click() 'edit selected

    On Error GoTo ErrorHandler:

    UserForm2.Label4 = ListBox1.List(ListBox1.ListIndex)
    UserForm2.TextBox1.Text = ListBox1.Column(0, ListBox1.ListIndex)
    UserForm2.TextBox2.Text = ListBox1.Column(1, ListBox1.ListIndex)
    UserForm2.Show
    Exit Sub
ErrorHandler:

    MsgBox "No Data or Click the info"
    Resume Next

End Sub

Code: Select all

Private Sub DeleteSelected_Click() 

    Dim i As Integer
    
    If MsgBox("Are you sure you want to delete the row?", vbYesNo + vbQuestion, "Delete row") = vbYes Then
        For i = 1 To Sheets("edit and delete").Range("B" & Rows.Count).End(xlUp).Row
            If Cells(i, 2) = ListBox1.List(ListBox1.ListIndex) Then
                Cells(i, 2).Select
                Cells(i, 2).Value = ""
                Cells(i, 3).Select
                Cells(i, 3).Value = ""
                Cells(i, 2).Value = ""
                Selection.delete
                Cells(i, 3).Value = ""
                Selection.delete
            End If
        Next i
    End If
End Sub

Code: Select all

          Cells(i, 2).Value = ""
          Selection.delete
          Cells(i, 3).Value = ""
          Selection.delete