Userform formatting currency

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,018
Office Version
  1. 2007
Platform
  1. Windows
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

Rich (BB code):
    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.

Rich (BB code):
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
 

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.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,605
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try these changes to your codes & see if will do what you want

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


VBA Code:
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
 
Last edited:
Solution

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,018
Office Version
  1. 2007
Platform
  1. Windows
Evening,
All worked a treat Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,133,621
Messages
5,659,922
Members
418,536
Latest member
Tezzies

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
Top