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.