corentint
New Member
- Joined
- Jan 31, 2022
- Messages
- 32
- Office Version
- 365
- Platform
- Windows
Hello to the community, hope all are fine.
Here is a peculiar problem in coding a userform.
Or should I envision a reprogramming of all calculations outside the textboxes, just coding the format for the end result?
Thank you for your help, in advance.
Corentint
Here is a peculiar problem in coding a userform.
- I have textboxes in a userform that display values (by that I mean only numerical values, no alpha tolerated )
- from a database Excel sheet into the userform.
- The database does not include calculations, so in populating the textboxes in the userform, I sometime have to do the calculation in the useform code.
- The calculations are done in the module that populates the useform
- Using the textboxes themselve in formulae.
- And then, formating the textbox according to what it contains ($, %, 0.0, etc.)
- Example:
- A calculated textbox (the content does not exist in the database):
- TextBox221.Value = BD(LineNb, 10) * BD(LineNb, 47) where BD is the cell from the database in Excel - this works (displays the expected result)
- The calculated textbox is then formated in a subroutine called after all calculated textboxes are completed (populated), as such:
- TextBox221.Value = Format(TextBox221.Text, "0.00" & FXL) where FXL is a string containing a currency symbol - one character
- This appears to work, but not always: sometimes it formats the value which is correct after calculation, BUT, sometimes it completely throws out the calculated result and I get weird number, always negative, and always values that are way above the expected results, i.e. :
- Good result: 189.00$
- Strange: -603245.000578 and not even formated.
- When I follow step by step (F8) the VBA code, nothing seems amiss, not erreur messages. There are no "On error resume next" code anywhere in the module and procedures.
- A calculated textbox (the content does not exist in the database):
- As I am writing this, I become more and more convinced I should not use textboxes to do direct computations between them, or that the formating should not be in a subroutine. Note that the subroutine is in the userform module, not outside of it. Not that this should matter, really...
Or should I envision a reprogramming of all calculations outside the textboxes, just coding the format for the end result?
Thank you for your help, in advance.
Corentint