Programming for Everybody: With source code Excel macro VBA tutorial| insert update delete in sheet excel using entry form

With source code Excel macro VBA tutorial| insert update delete in sheet excel using entry form

 



Private Sub CommandButton1_Click()

Dim sheet_v As Worksheet

Set sheet_v = ThisWorkbook.Sheets("sheet1")

Dim lrow_v As Long

lrow_v = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row

sheet_v.Range("A" & lrow_v + 1).Value = TextBox1.Value

sheet_v.Range("B" & lrow_v + 1).Value = TextBox2.Value

sheet_v.Range("C" & lrow_v + 1).Value = TextBox3.Value

sheet_v.Range("D" & lrow_v + 1).Value = TextBox4.Value

TextBox1.Value = ""

TextBox2.Value = ""

TextBox3.Value = ""

TextBox4.Value = ""

'MsgBox ("Data inserted successfully")

Call load_data

End Sub

Sub load_data()

Dim sheet_v As Worksheet

Set sheet_v = ThisWorkbook.Sheets("sheet1")

Dim lrow_v As Long

lrow_v = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row

sheet_v.Range("A" & lrow_v + 1).Value = TextBox1.Value

With ListBox1

.ColumnCount = 4

.ColumnHeads = True

.ColumnWidths = "70,90,90,90"

.RowSource = "sheet1!A2:D" & lrow_v

End With

End Sub


Private Sub CommandButton2_Click()

Dim lrow_v As Long

lrow_v = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row

Dim i As Long

For i = 2 To lrow_v

If Sheets("sheet1").Cells(i, 1).Value = id.Text Then

Sheets("sheet1").Cells(i, 1).Value = TextBox1.Text

Sheets("sheet1").Cells(i, 2).Value = TextBox2.Text

Sheets("sheet1").Cells(i, 3).Value = TextBox3.Text

Sheets("sheet1").Cells(i, 4).Value = TextBox4.Text

End If

Next i

End Sub


Private Sub CommandButton3_Click()

Dim lrow_v As Long

lrow_v = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row

Dim i As Long

For i = 2 To lrow_v

If Sheets("sheet1").Cells(i, 1).Value = id.Text Then

Rows(i).Delete

End If

Next i

TextBox1.Value = ""

TextBox2.Value = ""

TextBox3.Value = ""

TextBox4.Value = ""

id.Value = ""

End Sub


Private Sub ListBox1_Click()


End Sub


Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

TextBox1.Value = ListBox1.Column(0)

TextBox2.Value = ListBox1.Column(1)

TextBox3.Value = ListBox1.Column(2)

TextBox4.Value = ListBox1.Column(3)

id.Value = ListBox1.Column(0)

End Sub


Private Sub UserForm_Click()


End Sub


Private Sub UserForm_Initialize()

Call load_data

End Sub


No comments:

Post a Comment