Changing an Excel Userform does not work

[AyaSofea]

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

[Paul_Hossler]

str is Dim-ed as a String, but since the MsgBox is returning a Long (vbNo = 7 of vbYes = 6) it's coerced into a string = "7"

Since "7" is not the same as 7, it exits





Dim sonsat, lastRow As Long, str As String


.....

str = MsgBox("Are you sure?", vbYesNo, "")

If str = vbNo Then Exit Sub




You probably wanted:



if MsgBox("Are you sure?", vbYesNo, "") = vbNo Then Exit Sub



BTW, in



Dim sonsat, lastRow As Long, str As String

sonsat will default to a Variant, so if you wanted something else, you should Dim it accordingly



If that's not it, then put a breakpoint (F9) on the first line, run the button, and then single step (F8) to see what it's doing

[SamT]

What part does not Work?

Do you have Option Explicit?

Did you place a Breakpoint at the start of the sub then use F8 to step thru it?

Try:

Cells(sonsat, 2) = Me.TextBox1
'Etc

Neat Trick:

Rows(sonsat).Range("A1:O1").Font.ColorIndex = 18

New Code:

Set Sonsat = Sheets("Sheet1").Range("B7:B" & lastRow).Find(What:=ListBox1)
If sonsat is Nothing then
Exit Sub
Else
Set sonsat = sonsat.Offset(, -1).Resize(1, 15)

For i = 2 to 9
sonsat(cells(i) = Me.Controls("txtBox" & i - 1)
Next
For i = 10 to 15
sonsat(cells(i) = Me.Controls("txtBox" & i + 1)
Next
Sonsat.Font.ColorIndex = 18
end if

[AyaSofea]

To Paul and Sam,


Thank you for your replies. It already works actually, but it still has an error when I click change button in VBA user form.


Yes, Sam, I didn't have an Option Explicit in my code...


Can you figure out this problem...


So here I'm attached my file