updating values by vba

[skaswani]

dear sir,

i have problem making update values form
Ms Excel 2002, Windows Xp


** code to get values in to text box ***

Private Sub CommandButton1_Click()

Dim a
Dim i As Long
With Worksheets("sheet1")
a = Range(.Cells(1, 3), .Cells(1, 1).End(-4121))
End With
For i = 1 To UBound(a)
If a(i, 1) = TextBox1.Text Then Exit For
Next
If i > UBound(a) Then
MsgBox ("number not found"): Exit Sub
End If
TextBox3.Text = a(i, 3)
TextBox2.Text = a(i, 2)
End Sub


*** saving values ***
Private Sub CommandButton2_Click()
a(i, 2).Value = TextBox2.Value
a(i, 3).Text = testbox3.Value
End Sub


i am getting error here, please kindly help me out
thanks,

[Bob Phillips]

Is this the problem?



Private Sub CommandButton2_Click()
a(i, 2).Value = TextBox2.Value
a(i, 3).Text = Textbox3.Value '<<<<<<<< NOT testbox.Value
End Sub

[skaswani]

Run- time 424 Object required error now

i have change variable which you mention


plus, i have defined PUBLIC variable a on top

[Bob Phillips]

Maybe post the workbook?

[skaswani]

attached file

[Bob Phillips]

Yur Save button is disabled, so you can't save the changes.

[Bob Phillips]

This will sort the other problem



Public a As Range
Public i

Private Sub CmdCancel_Click()
refno.Enabled = True
End Sub

Private Sub CmdExit_Click()
End
End Sub

Private Sub CmdGO_Click()
'Dim a
'Dim i As Long
With Worksheets("sheet1")
Set a = .Range(.Cells(1, 8), .Cells(1, 1).End(xlDown))

For i = 1 To a.Rows.Count

If a.Cells(i, 2) = refno.Text Then Exit For
Next

If i > a.Rows.Count Then

MsgBox ("number not found"): Exit Sub
End If
End With

refno.Enabled = False

rem_name.Enabled = True
rem_name.Visible = True
ben_name.Enabled = True
ben_name.Visible = True
Cmdsave.Enabled = True
rem_name.Text = a.Cells(i, 3)
ben_name.Text = a.Cells(i, 4)

End Sub

Private Sub Cmdsave_Click()
a.Cells(i, 3).Value = rem_name.Text
a.Cells(i, 4).Value = ben_name.Text
End Sub