Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

show specific currency with numberformat in textbox on userform

0

hi

I  have  a  simple  question . I  try  showing  currency   in textbox1 on userform .for  instance  when I  write   the  number  like   this   1000  then   should  show  TRY 1,000.00 

I  tried  with  this 

Private Sub TextBox1_Change()
Me.TextBox1.Value = "TRY" & Format(CLng(TextBox1.Value), "#,##0.00")
End Sub
 but  it  gives   error mismatch 

can  anybody  guide  me   what  is  the  right way ,please?

Answer
Discuss

Answers

0
Selected Answer

Tubrak 

I guess you will use the numerical part from  Textbox1 so suggest you don't try to fornat the string (you'll have to remove the format if the value changes and for any calculations).

I would instead put a label with your currency "TRY"  to the left of Textbox1 and test/ convert the value from Textbox1. 

In the attached file, click the "Display Form" button on the sheet and a user form will be displayed with such a label. You can enter only numbers and a decimal separator (e.g. ".") but no alphabetic characters because of this event macro (commented for your benefit):

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii
    Case 48 To 57 'Allow integers 0 to 9
    Case vbKeyDecimal ' plus decimal separator
    Case vbKeyBack 'cursor etc. movement keys...
    Case vbKeyRight
    Case vbKeyLeft
    Case vbKeyInsert
    Case vbKeyDelete
    Case vbKeyShift

    Case Else 'but reject alpabetic and special characters etc.
        KeyAscii = 0
        Beep
        Exit Sub
End Select

End Sub
The following macro will add  the thousands separators as you type and allow you to type the decimal part (though it will stop after 2 decimal places):
Private Sub TextBox1_Change()

'Check flag to avoid repeating code
If boolSkip = True Then
    boolSkip = False
    Exit Sub
End If

'Set flag
boolSkip = True

With TextBox1

    'Check if >2dps and format text
    CursorPosition = .SelStart
    If .SelStart > InStr(1, .Text, ".") + 2 Then
        .Text = Left(.Text, .SelStart - 1)
        CursorPosition = .SelStart
        Beep
    End If

    .Text = Format(.Text, "#,##0.00")

    FirstDec = InStr(1, .Text, ".")

    'Check for decimal separator pressed
    If FirstDec < InStrRev(.Text, ".") Then 'decimal separator pressed so there are two
            .Text = Replace(.Text, "..", ".", 1) 'remove second
            .SelStart = FirstDec + 1 ' move cursor to decimal part
    End If
    'Re-position the cursor for next entry
    Select Case .SelStart
        Case Is < FirstDec
            .SelStart = FirstDec - 1
        Case Else
            .SelStart = Application.Max(CursorPosition, FirstDec - 1)
    End Select
End With

End Sub
The currency code isn't shown when you click Okay on the user form, for demonstartion purposes, a message delivers the result in your currency/format. The code for that is:
Private Sub CommandButton1_Click()
On Error Resume Next

If IsNumeric(TextBox1.Value) Then
    MsgBox "Price is TRY " & Format((TextBox1.Value), "#,##0.00")
    '<<Your code to work with numerical values>>
    Else
    '<<Your code to reject non- numerical values>>
End If

Unload Me ' close form

End Sub
where you should swap the bits bounded by << >> with your code.

Hope this helps.

Discuss

Discussion

Hi John
thanks  for  your  great  file  , but  actually  I  want  do  that  inside  the  textbox  not  by  message  .it  supposes  when write  the  numbers 100  then  show 100.00  and  when  write  10000 should  show  10,000.00 in textbox   and  so  on  because  I  have  project  I  would  implement   this  idea  on  my  project .
tubrak (rep: 22) Oct 22, '21 at 11:01 am
Tubrak, I tried "live" formatting in Textbox1 but swapping to #, ##0.00 was a problem (the cursor jumps to the after the decimsl places which felt unnatural) and I realised that it would be tricky to put the cursor in the right place (for later numbers). I decided to spend no more time on that but offered you a solution (assuming you would ignore the MsgBox demo and save formatting for the final result).

Good luck finding a solution but you may spend a lot of time creating problems for yourself.
John_Ru (rep: 6102) Oct 22, '21 at 12:05 pm
thanks for  your time  and  advice 
tubrak (rep: 22) Oct 22, '21 at 12:18 pm
Tubrak

Please see my revised Answer and file (which should work for you).

I found a code elsewhere which wasn't working fully but have fixed it and represented (so you can just cnahe the With statement for another TextBox).

If this works, kindly change your question title to say "Showing thousands and decimal places in a Userform TextBox" to guide others.Don't forget to Select the Answer if it works for you.
John_Ru (rep: 6102) Oct 23, '21 at 8:57 am
Tubrak, did that work? 
John_Ru (rep: 6102) Oct 26, '21 at 2:36 pm
Hi John,
first  I  really  sorry  about delaying  to  answer  you . I  hope to accept  my  apology.
second  of  course  it  works  perfectly.
third  I  deleted  command  button  and  Lable1 contain currency   and  I  move the  code  from commandbutton  to   TextBox1_Exit  event  it  shows  the  number  format  and  currency   without  any  problem .
fourth about   the  topic of  question   I  suggest like this " show specific currency with numberformat in textbox on userform " if  you  see  this  is  not  suitable  I  will publish you  suggestion 
finally   thanks  very  much  for  your  following   my  question and  solve  it.
tubrak (rep: 22) Oct 28, '21 at 4:58 am
No problem, thanks for changing the title and for selecting my Answer, Tubrak
John_Ru (rep: 6102) Oct 28, '21 at 5:06 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login