User Form Textboxes not summing values

[Ochimus]

Open User form from Sheet 1 and select Name from DropList.
Opening Balance calculates automatically from the TRANSACTION sheet, and shows the same value in the Closing Balance Textbox, with both boxes formatted as Currency.
That works perfectly.

If user then enters a value in either the CREDIT or DEBIT Textbox, the value should format as Currency, and be added to or deducted from Opening Balance to change the Closing Balance.
Despite multiple attempts with different Codes, this step is not working. It simply overwrites the Closing Balance with the Debit or Credit value as a number, not currency.

Relevant element of Code as follows:

Please Login or Register  to view this content.

Any solutions, suggestions or alternatives welcome as ever

Ochimus

[Kaper]

Have you tried also (similar to other places in your code):

Please Login or Register  to view this content.

[Mumps1]

Try the attached file. I changed the tboxCREDIT_Change to tboxCREDIT_AfterUpdate and tboxDEBIT_Change to tboxDEBIT_AfterUpdate. The reason for these changes is because "_Change" macros are triggered by the first entry in the textbox. For example, if you wanted to enter 25 in tboxCREDIT, as soon as you enter the number 2, the macro would be triggered and it would not allow you to enter the number 5. The "_AfterUpdate" version will require you to press the RETURN key after the value has been entered. I hope this makes sense.

[Ochimus]

Kaper,

Appreciate the prompt response, but the revised code doesn't work. The values in the Credit and Debit box are still numbers, not currencies, and they are not adding or deducting from the Opening balance.

Ochimus

[Mumps1]

Have you tried the file I posted in Post #3?

[Ochimus]

Mumps,

Thanks also for your prompt suggestion, which does change the Credit and Debit boxes to currency, but unfortunately it doesn't add or deduct them, because entering a value in either the Debit or Credit box triggers an "Error Code 13" on this line

Please Login or Register  to view this content.

It really ought to be straightforward, but I simply cannot see why it isn't working!

Ochimus

[Mumps1]

I tested the code in the file I attached and it worked properly for me. Are you trying the file I attached or are you trying the code on a different file?

[rorya]

If your regional settings are English, CDbl won't convert a US currency formatted value to a double (nor will Val). You'll need to remove the currency symbol using Replace or Mid, then convert to a number, add, and then format again.

[Mumps1]

Using @rorya suggestion, see if the attached file works for you.

[Ochimus]

Thank everyone, Mump's incorporation of rorya's version cracked it!

Intriguing point I can't follow is the "Sub SelectCells()" macro in the Module?

The only issue I can foresee is that entering the Debit value and going straight to the buttons won't change the closing balance, which requires the User to tab away from the Debit textbox. Just have to ensure they understand that!

Ochimus

[Mumps1]

Glad we could help.
Would it help if you added a "Label" beside the Debit value and Credit value boxes containing text such as "Press the RETURN key after entering the value."