#Value! when updating record in Userform

[farhad]

Good Day

I have this userform with multiple TextBoxes. TextBox17 has a numeric value in it. When I edit record the rewrite the updated information to theExcel Sheet, I am getting #value ! error on the value column which points to TextBox17.

The code for updating record is as follows:

Private Sub CommandButton16_Click()     'Update Edited Record
Application.ScreenUpdating = False
'Sheets("Sheet4").Unprotect
    Dim iRow As Long
    Dim ws As Worksheet
    Dim ALBTAG As String
    Dim CLoc As Range
    
Set ws = Worksheets("Sheet4")

    If ListBox1.Text = "" Then
        MsgBox "Select a record to edit", vbCritical
            Exit Sub
                End If
    
    ALBTAG = Me.TextBox6.Value
    
    Set CLoc = ws.Columns("A:CZ").Find(What:=ALBTAG, After:=ws.Cells(1), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
                               xlNext, MatchCase:=False, SearchFormat:=False)
   
    
    If CLoc Is Nothing Then
        iRow = ws.Cells(Rows.Count, 1) _
               .End(xlUp).Offset(1, 0).Row
    Else
        iRow = CLoc.Row
    End If
    
    
   
    ws.Cells(iRow, 8).Value = Me.TextBox7.Value
    ws.Cells(iRow, 9).Value = Me.TextBox8.Value
    ws.Cells(iRow, 10).Value = Me.TextBox9.Value
    ws.Cells(iRow, 11).Value = Me.TextBox10.Value
    ws.Cells(iRow, 12).Value = Me.TextBox11.Value
    ws.Cells(iRow, 13).Value = Me.TextBox12.Value
    ws.Cells(iRow, 14).Value = Me.TextBox13.Value
    ws.Cells(iRow, 15).Value = Me.TextBox14.Value
    ws.Cells(iRow, 16).Value = Me.TextBox15.Value
    ws.Cells(iRow, 7).Value = Me.TextBox16.Value
    ws.Cells(iRow, 17).Value = Format(Me.TextBox17.Value, "R# ##0.00")
   ' ws.Cells(iRow, 17).Value = Me.TextBox17.Value
    'ws.Cells(iRow, 17).Value = CDbl(Me.TextBox17.Value)
    ws.Cells(iRow, 21).Value = StrConv(Me.TextBox19.Value, vbProperCase)
    ws.Cells(iRow, 22).Value = Me.TextBox20.Value
    ws.Cells(iRow, 23).Value = Me.TextBox21.Value
    ws.Cells(iRow, 24).Value = Me.TextBox22.Value
    ws.Cells(iRow, 25).Value = Me.TextBox24.Value
    ws.Cells(iRow, 26).Value = Me.TextBox23.Value
   
 
 Call Main 'Progress Bar
        MsgBox "Record Updated ..."
          '  ListBox1.List = Sheets("Sheet4").Range("A4:CZ" & [a65536].End(3).Row).Value      'For refresh listbox
            ListBox1.List = Sheets("Sheet4").Range("A4:CZ" & [a1048576].End(xlUp).Row).Value
                Unload Me
                    UserForm1.Show
End Sub]

 

[Yongle]

I have been unable to replicate your issue

Try this line instead

ws.Cells(iRow, 17) = Me.TextBox17

or this

ws.Cells(iRow, 17) = Me.TextBox17.Text

If the cell contains a number, then insert this below to format it

ws.Cells(iRow, 17) = Format(ws.Cells(iRow, 17)), "R# ##0.00")