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

fixing error mismatch (sum format number with currency in textbox)

0

hi, experts

i  face   a problem  with sum  formatting   numbers  & currency  toghether into textbox on user form   and   gives  me  error  mismatch in this  line  

 Txt3.Value = CDbl(Txt1) + CDbl(Txt2)
Private Sub Txt1_Change()
  Txt3.Value = ""
  If Txt1 <> "" And Txt2 <> "" Then
    Txt3.Value = CDbl(Txt1) + CDbl(Txt2)
  End If
End Sub
Private Sub Txt2_Change()
  Txt3.Value = ""
  If Txt1 <> "" And Txt2 <> "" Then
    Txt3.Value = CDbl(Txt1) + CDbl(Txt2)
  End If
End Sub
Private Sub Txt2_AfterUpdate()
 With Me.Txt2
Dim s As String
s = "RM"
        If IsNumeric(.Value) Then
        .Value = s & " " & Format(CStr(.Value), "#,##0.00")
        End If
    End With
End Sub
Private Sub Txt1_AfterUpdate()
  With Me.Txt1
Dim s As String
s = "RM"
        If IsNumeric(.Value) Then
        .Value = s & " " & Format(CStr(.Value), "#,##0.00")
        End If
    End With
End Sub
Private Sub Txt3_Change()
  Txt3.Value = Format(Txt3.Value, "#,###.00")
End Sub

any idea   to   fix   the  trouple ?

thanks  in advance 

Answer
Discuss

Answers

0
Selected Answer

Hi Leopard (and Happy New Year!)

You get the error since you're trying to convert a string (like "RM 7.07) using CDbl- that doesn't work!

To fix it, you first need to remove the currency letters (added by Txt1 and Txt2 subs) then add the resultant numbers. I've done that but moved the summation code to this sub- see the additions in bold below, using the REPLACE string function:

Private Sub Txt2_AfterUpdate()
 With Me.Txt2
Dim s As String
s = "RM"
        If IsNumeric(.Value) Then
        .Value = s & " " & Format(CStr(.Value), "#,##0.00")
        End If
    End With

Txt3.Value = ""
  If Txt1 <> "" And Txt2 <> "" Then
    Txt3.Value = CDbl(Replace(Txt1, "RM", "")) + CDbl(Replace(Txt2, "RM", ""))
  End If
End Sub

I also needed to disable this sub (to prevent premature conversion as Txt2 is typed):

Private Sub Txt2_Change()
  'Txt3.Value = ""
 ' If Txt1 <> "" And Txt2 <> "" Then
 '   Txt3.Value = CDbl(Replace(Txt1, "RM", "")) + CDbl(Replace(Txt2, "RM", "RM", ""))
'  End If
End Sub

Plus -given a numeric value is in Txt3 after Sub Txt2_AfterUpdate() - I made this sub add the currency text like the other similar subs:

Private Sub Txt3_Change()
'  Txt3.Value = Format(Txt3.Value, "RM #,###.00")
With Me.Txt3
    Dim s As String
    s = "RM"
        If IsNumeric(.Value) Then
        .Value = s & " " & Format(CStr(.Value), "#,##0.00")
        End If
    End With
End Sub

The attached file should now work properly. Just click off Txt2 (into Txt1 say) and the total will appear in Txt3.

Hope this helps!

REVISION 1: Having said all that, do you realise that your method of adding the Ringit currency code can be shortened to a single line (after a With statement)? It's done like this:

.Value= Format(.Value,"""RM"" #,##0.00")

Important note: the text bit (RM) needs to be within two sets of quotation marks (and the whole custom number format has another set outside that. 

REVISION 2: The disadvantage of my suggestion (in Revision 1 above) is that RM has to be stated several times in each sub (so harder to edit if needed). Following a discussion point from Variatus, I've revised my code (and attached file) so that it becomes just two subs (each near identical) as below. I've removed the others.

Txt3 is a total (so should not be editable). Not sure why you want it only to appear when both values are input but in the UserForm, I've set the Enabled property of Txt3 to FALSE so that it can not be changed.

Private Sub Txt1_AfterUpdate()
With Me.Txt1
Dim s As String
s = "RM"
        If IsNumeric(.Value) Then
        .Value = s & " " & Format(CStr(.Value), "#,##0.00")
        End If
    End With

Txt3.Value = ""
  If Txt1 <> "" And Txt2 <> "" Then
    With Txt3
        .Value = CDbl(Replace(Txt1, s, "")) + CDbl(Replace(Txt2, s, ""))
        .Value = s & " " & Format(CStr(.Value), "#,##0.00")
    End With
  End If

End Sub

Private Sub Txt2_AfterUpdate()
 With Me.Txt2
Dim s As String
s = "RM"
        If IsNumeric(.Value) Then
        .Value = s & " " & Format(CStr(.Value), "#,##0.00")
        End If
    End With

Txt3.Value = ""
  If Txt1 <> "" And Txt2 <> "" Then
    With Txt3
        .Value = CDbl(Replace(Txt1, s, "")) + CDbl(Replace(Txt2, s, ""))
        .Value = s & " " & Format(CStr(.Value), "#,##0.00")
    End With
  End If
End Sub
Discuss

Discussion

wow ! very impressive    much  appriciate   i  just  have   a question  about  function replace  in this  line 
Txt3.Value = CDbl(Replace(Txt1, "RM", "")) + CDbl(Replace(Txt2, "RM", ""))

as  you  see  you  spcified   the  currency is  RM  but    why  the  code  doesn't  accept  the  varible  s instead of it  ,  i  tested  and  gives  me error  how     the  code can  know RM is  currency , but  i  think  working  normally    for  the  varible  s  thanks again
leopard (rep: 88) Jan 1, '21 at 4:21 pm
Thanks for selecting my answer, Leopard.  (Hope you saw my Revision 1 too). 
The reason "s" isn't recognised may be that it's a local variable (and has "life" only while the sub routine which created it is running. After that, it's gone!) Haven't got time to check it but REPLACE using s should work if s is defined in the same sub.using:
Txt3.Value = CDbl(Replace(Txt1, s, "")) + CDbl(Replace(Txt2, s, ""))
I think there's a tutorial on that (and using public variables which have longer "life") but I'm tired now, sorry. (I had a very long night yesterday! )
John_Ru (rep: 6102) Jan 1, '21 at 4:37 pm
thank for  remind  me  about   REVISION 1 actually  it really  missed  me  ,  yes  i tested  and  works    well done ! john 
greeting !  
leopard (rep: 88) Jan 1, '21 at 4:55 pm
Thanks Leopard, glad it worked 
John_Ru (rep: 6102) Jan 1, '21 at 5:02 pm
@John_Ru, Happy New Year, I wonder if you would like to review your code for Txt3.Value =, as repeated in the discussion above, in the light of the number format being "#,##0.00".
Variatus (rep: 4889) Jan 1, '21 at 7:32 pm
@Variatus- and a Happy New Year to you too! (Hope it's better than 2020!). I've revised my Answer (see Revision 2 and file). Hope that's what you meant.

@Leopard- new answer Revision 2 gives a simpler code (still based on yours) but with the Total in Txt3 protected.
John_Ru (rep: 6102) Jan 2, '21 at 2:35 am
Not sure where to find your revised code, John. My point is that CDbl(Replace("RM 10,123.33", "RM", "")) = 10
Variatus (rep: 4889) Jan 2, '21 at 8:28 pm
@Variatus
Thanks for the comments, which are addressed below:
My revised code is under REVISION 2 (at the end of my Answer) and within the file attached to the Answer.
Regarding your conversion point, if you click Leopard's "Command button" in the file and type in numbers, you'll see that the summing works (at least it does in Excel 2016).
Also I get these results from your example (not "10") from the Immediate Window, with and without the thousands separator::
Debug.Print CDbl(Replace("RM 10,123.33", "RM", ""))
 10123.33
Debug.Print CDbl(Replace("RM 10123.33", "RM", ""))
 10123.33  

In the revised code, I stuck with Leopard's variable s (for the reason stated in Revision 2) and it works.
John_Ru (rep: 6102) Jan 3, '21 at 3:20 am
@john     about  this   part  of  revision2   "The disadvantage of my suggestion (in Revision 1 above) is that RM has to be stated several times in each sub "    i  know    that    first  of  all not  work   when  you  suggest  me    in  rivision  1   but   i   change    many   time   about RM    and  works   so  i  don't   mentioned  that 
anyway  thanks    john & variatus   
leopard (rep: 88) Jan 3, '21 at 2:18 pm
Add to Discussion


Answer the Question

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