Userform formatting currency

[ipbr21054]

Hi,
On my userform i have TextBox4 which i would like that when i enter 15 into it then i should see £15.00
I am using this code shown below but it only changes the 15 to £15

    Private Sub TextBox4_Change()
        TextBox4 = UCase(TextBox4)
        TextBox4.Value = Format(TextBox4.Value, "£###,##")
    End Sub

Once i use the command button to send values from userform to worksheet i need that value on the worksheet to also show the currency format like so £15.00 etc
This is the code in use for the transfer from userform to worksheet.

Private Sub CommandButton1_Click()
    Dim i As Long, x As Long
    Dim LastRow As Long
    
    With Sheets("G INCOME")

        If TextBox1.Value = "" Then
            MsgBox "NO CUSTOMER'S NAME WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox1.SetFocus
            Exit Sub
        ElseIf TextBox2.Value = "" Then
            MsgBox "NO ADDRESS WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox2.SetFocus
            Exit Sub
        ElseIf TextBox3.Value = "" Then
            MsgBox "NO POST CODE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox3.SetFocus
            Exit Sub
        ElseIf TextBox4.Value = "" Then
            MsgBox "NO CHARGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox4.SetFocus
            Exit Sub
        ElseIf TextBox5.Value = "" Then
            TextBox5.SetFocus
            MsgBox "NO MILEAGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            Exit Sub
        End If

        LastRow = .Cells(Rows.Count, "N").End(xlUp).Row + 1
       
        Application.ScreenUpdating = False
        For i = 1 To 5
            .Cells(LastRow, i + 13).Value = Me.Controls("TextBox" & i).Value
        Next i
        With .Cells(LastRow, 14).Resize(, 5)
            .Font.Name = "Calibri"
            .Font.Size = 11
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Borders.Weight = xlThin
            .Interior.ColorIndex = 6
        End With
        .Cells(LastRow, "N").HorizontalAlignment = xlLeft
        Application.ScreenUpdating = True
       
        If .AutoFilterMode Then .AutoFilterMode = False
            x = .Cells(Rows.Count, 4).End(xlUp).Row
            .Range("N4:R" & x).Sort Key1:=.Range("N4"), Order1:=xlAscending, Header:=xlGuess
       
        Unload AddCustomer
        MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"

        .Range("N4").Select
       
    End With
    End Sub

TextBox4 value will be placed into the cell at column Q

Please advise.

Many Thanks

 

[dmt32]

Hi,
try these changes to your codes & see if will do what you want

Private Sub TextBox4_AfterUpdate()
    TextBox4.Value = Format(TextBox4.Value, "£#,##0.00")
End Sub

Private Sub CommandButton1_Click()
    Dim i           As Integer
    Dim LastRow     As Long
    Dim wsGIncome   As Worksheet
    Dim arr(1 To 5) As Variant
    Dim Prompt      As String
  
    Set wsGIncome = ThisWorkbook.Worksheets("G INCOME")
  
    For i = 1 To 5
        Prompt = Choose(i, "CUSTOMERS'S NAME", "ADDRESS", "POST CODE", "CHARGE", "MILEAGE")
        With Me.Controls("TextBox" & i)
            If Len(.Value) = 0 Then
                MsgBox "NO " & Prompt & " & WAS ENTERED", 16, Prompt & " Empty MESSAGE"
                .SetFocus
                Exit Sub
            Else
                If InStr(1, .Value, "£") = 1 Then
                    arr(i) = CCur(.Value)
                ElseIf IsDate(.Value) Then
                    arr(i) = DateValue(.Value)
                ElseIf IsNumeric(.Value) Then
                    arr(i) = Val(.Value)
                Else
                    arr(i) = .Value
                End If
            End If
          
        End With
    Next i
      
         Application.ScreenUpdating = False
        With wsGIncome
            LastRow = .Cells(.Rows.Count, "N").End(xlUp).Row + 1
          
            With .Cells(LastRow, 14).Resize(, UBound(arr))
                .Value = arr
                .Font.Name = "Calibri"
                .Font.Size = 11
                .Font.Bold = True
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Borders.Weight = xlThin
                .Interior.ColorIndex = 6
            End With
          
          
            .Cells(LastRow, "N").HorizontalAlignment = xlLeft
         
          
            If .AutoFilterMode Then .AutoFilterMode = False
            x = .Cells(Rows.Count, 4).End(xlUp).Row
            .Range("N4:R" & x).Sort Key1:=.Range("N4"), Order1:=xlAscending, Header:=xlGuess
         
            .Range("N4").Select
          
        End With

       Unload Me
        Application.ScreenUpdating = True
         MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"

    End Sub

Dave

 

[ipbr21054]

Evening,
All worked a treat Thanks

 

[dmt32]

Evening,
All worked a treat Thanks

welcome appreciate feedback

Dave