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

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.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
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,563
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,563
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,138
Messages
5,570,385
Members
412,321
Latest member
Yusuf_A
Top