PDA

View Full Version : Solved: More headache w/ xl 2007 macros - HELP!



joelle
12-19-2008, 03:35 PM
Hello Experts,

Now I have to handle a big company spreadsheet in both versions: 2003 & 2007. Sometimes, all macro buttons are clickable and macros work well but most times, after I converted my xl-2003 ws to xl 2007, I *HAVE* to re-attach each macro to each button, and there are 50 of them.

What is the secret please?? or how do i convert more properly.
What I did was: open the 2003 ws in xl-2007 and save it with ext .xlsm

Fyi, I do not use the button from the "Form' menu. The buttons I have is a basic shape, say, circle.

Please help. I am so sick of having to re-attach each of the 50 macros biweekly.

JimmyTheHand
12-19-2008, 11:22 PM
Do you absolutely need to convert the macros? Can't you separate data from code?
I mean, how about making a front end in XL2007, which doesn't need to be changed or converted between versions. It would work from the ever changing data, and spit out the result in both versions.

Jimmy

joelle
12-22-2008, 05:38 PM
Hello Jimmy,
Thanks for the reply (sorry I was sick the past few days)
I did try to make the front end from xl2007, but a lot of people in my company are still using xl2000-2003, and when i saved it in the compatible mode with xl2000-2003, they cannot run those macros.
But I swear, a couple of times I opened my wb in xl2007 and saved it with the .xlsm ext, they worked just fine ??? and now all of a sudden the macros stop running in xl2007, except, yes, except the ones that have the buttons from the "Form" menu.
I wonder what can be a workaround ... please help.

JimmyTheHand
01-05-2009, 08:19 AM
Hello Joelle,

Now it's I who hasn't been around for some time.

I'm not sure if I use the right term here. By front end I mean THE program that deals with the data. The data changes regularly, the program is always the same. The program is located in one (constant) workbook, the data comes in daily, weekly, monthly, etc. files. The program can always process the data file. The program contains no data, only the VBA code. The data file contains no VBA code, only data.

If the program must be used in two different environments (i.e. xl2003 and xl2007) then two versions should be made. So you create Version A in xl2003, and Version B in xl2007. A and B are essentially the same, they do the same, they only do it in different environments. You send Version A to all those people that have xl2003, and Version B to those that have xl2007. If the programs are OK, your colleauges all should be able to process the same data files, and get the same results.


As an alternative option, you can try installing Microsoft's compatibility kit (http://office.microsoft.com/en-us/products/HA101686761033.aspx).
It enables xl2003 users to open xl2007 documents.

HTH

Jimmy

joelle
01-05-2009, 09:36 AM
Hi Jimmy,

Yes - I believe you got my point.
And the program must be used in two different environments (i.e. xl2003 and xl2007), and so 2 versions should be made. But thats my headache when I try to avoid 2 versions. My wb is big and things would go out of sync easily with 2 versions when macros in V1 dont work in V2.
But the workaround I found, long and tedious but one-time-only, are:
1. recreate all macros using the "button" from the "Form" menu
2. re-attach the 70 some macro to each button

then, they all live in peace when I open the WB in xl2007. I tried to avoid the two crazy redo's above but looks like this is the only option.

Thank you for your posts and the effort to help. I appreciate it.

joelle