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