how ignore currency in textbox when read amount ?

[abdelfattah]

hello
actually I have textbox contains like this LYD 1,324,555.00 the textbox link with lable and function to read amount , but the problem is in currency LYD . the function can't read it because of the currency how I can avoid reading currency to fix the problem ?
by the way the code works if there is no currency in textbox .
here is my code

Private Sub TextBox49_Change()

TextBox49 = Format(TextBox49, "#,##0.00")
Label47.Caption = " " & "amount only" & " " & _
kh_TextNum(TextBox49, 0, "dinner", "dananner", "only cent,,,", 3, "cents", 1)

With Me.TextBox49
Dim s As String

s = "LYD"
        If IsNumeric(.Value) Then
        .Value = s & " " & Format(CStr(.Value), "#,##0.00")
        End If
    End With

End Sub

thanks

 

[Micron]

Not sure I understand. What is this function you refer to - kh_TextNum? This is on an Excel userform?
If the label could contain "LYD" instead then you'd have no problem? If it cannot, then perhaps
If IsNumeric(left(.value,4)) OR IsNumeric(.Value) Then ?

Some of your code doesn't seem right. Why format the textbox 2x or concatenate spaces when you could just put them in the string value?

That would presume any currency prefix is always 3 letters. If not, I think you need a different approach altogether.

 

[Micron]

EDIT - upon review, I think your problem is in the function code. You should post that and maybe still answer the questions? Nor would you use Left function for this.

 

[Micron]

After playing with your code, perhaps this is what you need:

Private Sub TextBox49_Change()
Dim str As String

str = Me.TextBox49
If Not IsNumeric(str) Then 'if textbox value is not numeric then get the number value from the 4th character to the end
  str = Mid(str,4)
'now pass the number portion only to the function
  Label47.Caption = " amount only " & _
  kh_TextNum(str, 0, "dinner", "dananner", "only cent,,,", 3, "cents", 1)
Else 'the textbox value is a number, so pass that number to the function
  Label47.Caption = " amount only " & _
  kh_TextNum(str, 0, "dinner", "dananner", "only cent,,,", 3, "cents", 1)
  Me.TextBox49 = "LYD" & str 'now concatenate the textbox number value to the string LYD
End If

End Sub

 

[abdelfattah]

awesome ! this is exactly what I want.
many thanks for your help

 

[Micron]

So glad to know it worked for you because it was more or less a guess as to what you needed.

 

[abdelfattah]

sorry I came back but I face problem with numberformat . when show LYD and fill number in textbox49 1100 should be LYD 1,100.00 but it stays LYD 1100 despite of using this

Private Sub TextBox49_AfterUpdate()
  Me.TextBox49 = Format(TextBox49, "#,##0.00")
End Sub

any idea ,please?

 

[Siddharth Rout]

hello
actually I have textbox contains like this LYD 1,324,555.00 the textbox link with lable and function to read amount , but the problem is in currency LYD . the function can't read it because of the currency how I can avoid reading currency to fix the problem ?
by the way the code works if there is no currency in textbox .
here is my code

Private Sub TextBox49_Change()

TextBox49 = Format(TextBox49, "#,##0.00")
Label47.Caption = " " & "amount only" & " " & _
kh_TextNum(TextBox49, 0, "dinner", "dananner", "only cent,,,", 3, "cents", 1)

With Me.TextBox49
Dim s As String

s = "LYD"
        If IsNumeric(.Value) Then
        .Value = s & " " & Format(CStr(.Value), "#,##0.00")
        End If
    End With

End Sub

thanks

I usually handle this in a different way. I only allow numeric values in the textbox. This way you will never have to cater to text at whatever position. You are assuming that the user is going to enter the data as LYD 1,324,555.00. That is a very big assumption. What if the user enters 1,324,555.00 LYD?

To prevent such issues allow only numeric entry in the textbox. Here is an example.

Private Sub TextBox49_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
        Case vbKey0 To vbKey9, vbKeyBack, vbKeyClear, vbKeyDelete, vbKeyLeft, vbKeyRight, vbKeyUp, vbKeyDown, vbKeyTab
            If KeyAscii = 46 Then If InStr(1, TextBox49.Text, ".") Then KeyAscii = 0
        Case Else
            KeyAscii = 0
            Beep
    End Select
End Sub

 

[Micron]

I don't think you can get that format with text mixed with numbers, which is why I concatenated the textbox value:
Me.TextBox49 = "LYD" & str
I forgot to post that my thought was for you to set the format of the textbox using the property sheet but that probably would not work either. So maybe format the str variable first then do the concatenation.

 

[abdelfattah]

@Siddharth Rout

What if the user enters 1,324,555.00 LYD?

To prevent such issues allow only numeric entry in the textbox. Here is an example.

thanks for your notice , but still doesn't treat problem the numberformat