#Value! when updating record in Userform

farhad

New Member
Joined
May 18, 2009
Messages
41
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:

Code:
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]
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
I have been unable to replicate your issue :confused:

Try this line instead
Code:
ws.Cells(iRow, 17) = Me.TextBox17
or this
Code:
ws.Cells(iRow, 17) = Me.TextBox17.Text

If the cell contains a number, then insert this below to format it
Code:
ws.Cells(iRow, 17) = Format(ws.Cells(iRow, 17)), "R# ##0.00")
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top