Textbox .value vs .text recent change?

[glfiedler]

I use office 365 on a windows 10 system that Microsoft automatically updated yesterday.
An excel project I wrote some 18 years ago is, today, having issues with userform textboxes. This project has worked well for years and the vba errors occurring today have not been modified.
The error is "type mismatch".
Back so many years ago when I was just learning vba programming I was not aware of the slight difference between the "value" and "text" properties of textboxes. I have learned much over the years, thanks to many of you on this forum, and I thought I now had a pretty good idea of the difference.
Here is a typical section of code where the User enters some text along with a dollar amount in a userform textbox. The code basically changes the sign of the dollar amount and places the text and amount in a worksheet.

With Checking.Range("iDiscoverDate")
            .Offset(blank, 0).Value = PurchaseFM.ActionDate.Text
            .Offset(blank, 1).Value = PurchaseFM.PurDescription.Text
            .Offset(blank, 2).Value = PurchaseFM.Amount.Text * -1
        End With

I changed the code, today, by replacing .text with .value in the "amount" equation:

With Checking.Range("iDiscoverDate")
            .Offset(blank, 0).Value = PurchaseFM.ActionDate.Text
            .Offset(blank, 1).Value = PurchaseFM.PurDescription.Text
            .Offset(blank, 2).Value = PurchaseFM.Amount.Value * -1
        End With

I still get the mismatch error. Do any of you have any ideas why this is not working?

 

[GWteB]

Did you step through the code by setting a breakpoint on the With Checking.Range line and stepping through the code with F8 key? If so, on which line does the type mismatch error occur?

 

[Yongle]

One way ...

.Offset(blank, 2).Value =  Val(PurchaseFM.Amount.Text) * -1

 

[GWteB]

... or

.Offset(blank, 2).Formula = PurchaseFM.Amount.Value * -1

 

[mikerickson]

My experience with .Value vs. .Text is the other way, .Text is safer than .Value.
Mostly with ComboBoxes and ListBoxes, sometimes (when the user hasn't made a selection) the .Value is data type Empty, but .Text is vbNullString, so I test against .Text rather than .Value.
And, it's always wise to use Val when doing arithmetic on a userform control's value.

 

[glfiedler]

Thank you GWteB. I did not single step through the code but I will do so tomorrow.

Thank you Yongle. Using the "val" function was going to be my next attempt at a solution. However, I decided to post my question before making changes because until today the code I had worked for YEARS. I guess my real question is whether or not a Microsoft update changed some hard to find setting like "treat text as a number in equations" In a worksheet you can get a little sloppy with doing arithmetic using a mixture of text and numbers. VBA may not be as tolerant, I do not know, but it seems to have changed today. Maybe I changed something without knowing it. It would not be the first time!

Thank you mikerrickson. I have seen some posts on the web that discuss the finer points of value and text similar to your comments. They seemed clear enough and I thought I understood. Since the object is a "TEXTBOX" I would think the "Value" property would be a "TEXT" string regardless of whether or not it looked like a number. So really I don't think it would make any difference when using arithmetic; the formula would always be a combination of text and numbers. Using Yongle's val( ) suggestion is positively correct, no question about it. Just why all of a sudden does mixing text and numbers not work?

 

[GWteB]

You are welcome and thanks for letting us know.
(imho it's sometimes not worth trying to solve a mystery, if you one way or the other have gained a solution for the issue....)

 

[glfiedler]

You are welcome and thanks for letting us know.
(imho it's sometimes not worth trying to solve a mystery, if you one way or the other have gained a solution for the issue....)

Yes, sometimes it is just better to stop banging your head against the wall! It just goes against my old engineer's brain to do something and not understand.

 

[Yongle]

Yes, sometimes it is just better to stop banging your head against the wall!

There are a lot of inconsistencies that you have to "accept" - the difficulty is in remembering when what works

Excel is a "numbers" toy and handling "text" was a bit of a bolt on
I am sure that Microsoft regrets many of the decisions made that (at the time) appeared convenient to the user

In VBA
If IsEmpty(Container) can return a different value to If Container is Empty even if the container is the same container emptied with Container = Empty

In Excel
Ony a politician thinks that ="Truth"="TRUTH" is TRUE
Lines of code were added to convert characters in order to FORCE it to be true! So it therefore FALSE
We could have all lived with a function =ANYCASE("Truth")=ANYCASE("TRUTH")

 

[mikerickson]

...In Excel
Ony a politician thinks that ="Truth"="TRUTH" is TRUE
Lines of code were added to convert characters in order to FORCE it to be true! So it therefore FALSE
We could have all lived with a function =ANYCASE("Truth")=ANYCASE("TRUTH")

In worksheet formulas ="Truth"="TRUTH" is TRUE, =EXACT("Truth","TRUTH") is FALSE.