AyaSofea
12-09-2017, 05:29 PM
Hi,
I'm currently making Data Entry Userform in Excel using VBA.Basically, I used VBA to make a Userform to search, change, add and delete the data in Excel Worksheet. But I'm having a problem with change button does not work. I'm stuck on how to solve this problem. Can you help me, Thank you.
Change Button Code:
'Button change (CB2)Private Sub ChangeButton_Click()
Dim sonsat, lastRow As Long, str As String
If TextBox1.Text = "" _
Or TextBox3.Text = "" Then
MsgBox "Item Is Not Selected To Change", vbCritical, ""
Exit Sub
End If
str = MsgBox("Are you sure?", vbYesNo, "")
If str = vbNo Then Exit Sub
lastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("Sheet1").Range("B7:B" & lastRow).Find(What:=ListBox1.Text, LookIn:=xlValues, LookAt:=xlWhole).Activate
sonsat = ActiveCell.Row
Cells(sonsat, 2) = TextBox1
Cells(sonsat, 3) = TextBox2
Cells(sonsat, 4) = TextBox3
Cells(sonsat, 5) = TextBox4
Cells(sonsat, 6) = TextBox5
Cells(sonsat, 7) = TextBox6
Cells(sonsat, 8) = TextBox7
Cells(sonsat, 9) = TextBox8
Cells(sonsat, 10) = TextBox11
Cells(sonsat, 11) = TextBox12
Cells(sonsat, 12) = TextBox13
Cells(sonsat, 13) = TextBox14
Cells(sonsat, 14) = TextBox15
Cells(sonsat, 15) = TextBox16
Range("A" & sonsat & ":O" & sonsat).Font.ColorIndex = 18
MsgBox "Item Has Been Updated", vbInformation, ""
Call listbox1_refresh
Call text_boxes_clear
ListBox1.Clear
DeleteButton.Enabled = False
ChangeButton.Enabled = False
SaveButton.Enabled = True
End Sub
I'm currently making Data Entry Userform in Excel using VBA.Basically, I used VBA to make a Userform to search, change, add and delete the data in Excel Worksheet. But I'm having a problem with change button does not work. I'm stuck on how to solve this problem. Can you help me, Thank you.
Change Button Code:
'Button change (CB2)Private Sub ChangeButton_Click()
Dim sonsat, lastRow As Long, str As String
If TextBox1.Text = "" _
Or TextBox3.Text = "" Then
MsgBox "Item Is Not Selected To Change", vbCritical, ""
Exit Sub
End If
str = MsgBox("Are you sure?", vbYesNo, "")
If str = vbNo Then Exit Sub
lastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("Sheet1").Range("B7:B" & lastRow).Find(What:=ListBox1.Text, LookIn:=xlValues, LookAt:=xlWhole).Activate
sonsat = ActiveCell.Row
Cells(sonsat, 2) = TextBox1
Cells(sonsat, 3) = TextBox2
Cells(sonsat, 4) = TextBox3
Cells(sonsat, 5) = TextBox4
Cells(sonsat, 6) = TextBox5
Cells(sonsat, 7) = TextBox6
Cells(sonsat, 8) = TextBox7
Cells(sonsat, 9) = TextBox8
Cells(sonsat, 10) = TextBox11
Cells(sonsat, 11) = TextBox12
Cells(sonsat, 12) = TextBox13
Cells(sonsat, 13) = TextBox14
Cells(sonsat, 14) = TextBox15
Cells(sonsat, 15) = TextBox16
Range("A" & sonsat & ":O" & sonsat).Font.ColorIndex = 18
MsgBox "Item Has Been Updated", vbInformation, ""
Call listbox1_refresh
Call text_boxes_clear
ListBox1.Clear
DeleteButton.Enabled = False
ChangeButton.Enabled = False
SaveButton.Enabled = True
End Sub