Problem with existing VBA to format currency

[harryv27]

Hi,

I have written some code, that once a user selects a currency from the drop down, then all worksheets are scanned and all fields with a currency format are fomatted accordingly.
So for example if a user selects United Kingdom, all cells with financial or currency information are formatted with the currency £.

For some reason i had this code in another worksheet which was scanning multiple worksheets and worked perfectly. Now i only have one active worksheet and it nots working and i cant seem to pinpoint what the problem is,

can anyone help?

PS: i have uploaded the worksheet

[xlnitwit]

Hi,

Your sheet is called "Rough" not "Rough1" which is preventing the code from working. That would be readily apparent if you did not have On Error Resume Next simply ignoring all errors- not good practice.

[harryv27]

Thanks for the heads up but even after changing the reference to the Rough worksheet its still not working.

When you select the currency and then press the set button, the currency is chaging in cells F7, but it is not adjusting the formatting of the E29, E31,E34.

Basically these fields should be formatted according to cell F7, any ideas?

[xlnitwit]

Apologies, my cursory look was at the wrong macro!

You have this line in the convert currency macro

Please Login or Register  to view this content.

so only the Rough sheet is actually being processed.

[harryv27]

ok im guessing this is a dumb question, but how do i fix this?
for this specific example i only want the input_values sheet to be checked and corrected.

In the future i will have more worksheets so good to know how to do multiple worksheets in the future too.

Can you be a bit more specific, as Macros are really not my thing

thanks,

[xlnitwit]

For this example then

Please Login or Register  to view this content.

That will skip any sheet that is not called "Input Values". You might also of course change the code to only look at that specific sheet but keeping the loop will make it simpler to adjust for more sheets in the future.

[harryv27]

ok perfect this is working nicely, but its adding junk characters at the bottom,

its putting in columsn F and G 44, junk characters like :
,0 F2
P0 F2
P0 F2
P0 F2
P0 F2
F2 F2
F1 F2


any ideas why its doing this?

[xlnitwit]

I'm not seeing any such characters. Which currency selections are you making?

[harryv27]

any selection i make, here attaching the file with the code you suggested. probably have put the code in wrong in the macro

[xlnitwit]

I might suggest reducing your code to something a little more compact, thus

Please Login or Register  to view this content.

[harryv27]

works like a charm thank you so much.

Out of curiosity, if i had to add more worksheets to the file, what would i need to change???

thanks,

[xlnitwit]

If you want them to be included in the processing, you need only add them to this line

Please Login or Register  to view this content.

so that it would become

Please Login or Register  to view this content.

[harryv27]

thank you so much for the replies. have a nice day

[xlnitwit]

You're most welcome.