Format Textbox as currency

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,338
Office Version
  1. 2016
Platform
  1. Windows
I'm trying to automatically format a textbox on a userform so that it displays currency but can't get it to do what I want.

As an example, if the user enters 12345 then I want the Textbox to display "£123.45". If they entered 1234 then I want the Textbox to display "£12.34".

I've got this so far but it's producing "£12,345":

VBA Code:
    If IsNumeric(TextBox1) Then
        TextBox1 = FormatCurrency(TextBox1, "0.00") 
    End If

Can someone assist?
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
    If IsNumeric(TextBox1) Then
        TextBox1 = FormatCurrency(TextBox1/100, "0.00") 
    End If
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,338
Office Version
  1. 2016
Platform
  1. Windows
How about
VBA Code:
    If IsNumeric(TextBox1) Then
        TextBox1 = FormatCurrency(TextBox1/100, "0.00")
    End If
Cheers Fluff - however, if I type '12345' I get "£123" - any ideas?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
TextBox1 = FormatCurrency(TextBox1 / 100)
 
Solution

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,338
Office Version
  1. 2016
Platform
  1. Windows
That's done it - thanks for your help!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,279
Messages
5,571,288
Members
412,375
Latest member
BRJoeyMelo
Top