Spreadsheet formatting issue

    A workbook I have is for a Punters Club and is basically the same as the first one I created about 13 years ago, with additions as I have gone along to automate stuff. The code (where I believe the issue I have stems from) was not affecting anything until around 24 months or so ago (maybe a bit longer) when all of a suddenly the number format for the entire workbook changed (in currency type cells) from Currency ($1234.00 , -$1234.00) to Custom ($1234.00, ($1234.00)). I have a feeling this issue became so during Excel 2013. I have tried it on different computers, with excel 2013 & 2016 but no difference. Now I have changed all the numbers back to Currency (as I want) but as soon as I re-open the workbook, (after saving from previous changes) the ($*****) format returns. It now appears there is something in the code (attached) that seems to be forcing a change to the Custom format, however, if so, it never used to. The code was designed so as to write a dollar and cent figure into words for ‘cheques’ I ‘present’ at the end of the year (the formula is =spellnumber(cell) for each of the 'cheques' required). These cheques are on 1 worksheet only, and reference another couple of sheets to get the figures/dates etc.


    I have commented the code out in the spreadsheet, changed the format of one number, saved, closed and re-opened with the currency format remaining – so it appears it is the code making the change (although the code is only a function and not a sub routine). But I cannot read (or completely understand) where the code is now forcing a change of the entire workbook rather than the one sheet it should be. I would appreciate of someone could advise where in the code the change is called, and how I would go about changing the format ‘back’ to currency (or not changing the format on all sheets).


    Thanks in advance for any assistance.


    Ron

    Can you attach a sample workbook so that we can try to replicate your problem?

    As Barry said, attaching an example workbook would help us to help you... ;)


    I have read through the code you put in the text file and I spotted a flaw. What happens if you submit a call to SpellNumber with a number larger than 999.99? In order for that to work there must be other code at work somewhere to handle the larger values.
    Also, the variable declaration is wonky. Unless for some reason there is a broader-scope array declared elsewhere, but even then it isn't used in the code shown.


    In any case, nothing in that code has anything to do with the format of the numbers - it just converts a numeric value to a text string;

    Well some further trials has narrowed the problem down to a pivot table (on sheet 'Receipts') which is driven by a macro. All the macro is supposed to do is unprotect the sheet, refresh the pivot table, protect the sheet and save the workbook. Somehow the format is being applied across the workbook. I have tried changing the field settings value of the pivot table 'sub total' to currency however clicking the macro button and closing does nothing to change the problem. I have found that if I apply the field setting of currency to the pivot table (and anywhere else in the book), then manually refresh the pivot table, manually protect the sheet and then manually save the workbook - once I reopen everything is fine. It appears to be totally linked to the macro but I cannot see why.


    Attached is a copy of the workbook


    Thanks
    Ron

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login