Value in userform isnt entered correctly when sent to worksheet

[ipbr21054]

Morning,
I am using the code below.

I have a userform with 4 Textboxes.
The Textboxes are tied to my worksheet like so.

TEXTBOX 1 COLUMN A
TEXTBOX 2 COLUMN B
TEXTBOX 3 COLUMN C
TEXTBOX 4 COLUMN D

I enter a value in each Textbox then send to worksheet,all are fine apart from TEXTBOX4 in which if i type say ABC 123 when its sent to the worksheet i see 0 in the cell as opposed to whatever i type.
It also places the 0 to either the left or right ?
The cells on worksheet are formatted as Text & to center the value
Do you see why the issue happens

Please see screenshot

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 4
       With Me.Controls("TextBox" & i)
            If .Text = "" Then
                MsgBox "MUST SELECT ALL OPTIONS", 48, "CLONING TRANSFER SHEET"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
   
    ControlsArr = Array(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.TextBox4)
   
    With ThisWorkbook.Worksheets("KEYCODES")
        .Range("A3").EntireRow.Insert Shift:=xlDown
        .Range("A3:D3").Borders.Weight = xlThin
        .Range("A3:D3").Font.Size = 14
   
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 3
               .Cells(3, i + 1) = Val(ControlsArr(i))
               ControlsArr(i).Text = ""
            Case Else
               .Cells(3, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i
    End With
   
    Application.ScreenUpdating = False
    With Sheets("KEYCODES")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A2:D" & x).Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess
        Unload KeyCodesForm
    End With
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    Sheets("KEYCODES").Range("A3").Select
    MsgBox "DATABASE NOW UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
   
End Sub

 

[RoryA]

You're using Val(ControlsArr(i)) for the last item in the array. Val attempts to convert text to a number and returns 0 if it can't, which is why you get 0 for ABC 123. Why are you using Val?

 

[ipbr21054]

I copied the code from another form i use

 

[RoryA]

It doesn't seem like you need it here at all.

 

[ipbr21054]

So is this correct ?

        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 3
               .Cells(3, i + 1) 
               ControlsArr(i).Text = ""
            Case Else
               .Cells(3, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select

 

[RoryA]

No - you're actually doing the same thing for all the controls so it's much simpler - just:

        For i = 0 To UBound(ControlsArr)
           .Cells(3, i + 1) = ControlsArr(i).Text
           ControlsArr(i).Text = ""
    Next i

 

[ipbr21054]

OK now changed thanks