Excel 2007 : Macro stopped working

**jaw0001** · 10-13-2010, 06:43 AM

Usually I can solve most programming issues by surfing the web, but this one is different.
About 2 months ago my work moved from Office 2003 to Office 2007. My macros worked for a little while but then some stopped. Basically any macro that executes on a selection in a worksheet will now only partially work on the first cell. For instance:

Please Login or Register  to view this content.

This is a macro I have used without hickup for 2+ years, until recently.

Basically this is a complicated way of writing a cell formula in a cell. It was made more complicated because I had to trick it into updating when the referenced cell updated. If I enter "A4" in cell B5 and highlight B5 and run this macro, it will write a cell formula to B5 which will return the cell formula of A4. Fine and good. When the formula in A4 changes, B5 is automatically updated. If you highlight a range (always had to drag to the left and down to work properly), it would do the same for all cells in the range. Now it only does the first cell. The funny thing is it never executes past the line:

Please Login or Register  to view this content.

I can put a msgbox after this line and it will never come up, before the line it will.

What gives?

BTW: Function cellformula:

Please Login or Register  to view this content.

**jaw0001** · 10-13-2010, 07:25 AM

OK. Here is a sample. A text file of personal macro is also inserted. Unfortunately this file worked the right way when I made it.

So it's an intermittent problem. Maybe a windows setting, corporate security settings, or? Maybe my seed file was made in Excel 2003 and I need to throw it away? Any suggestions?

Thanks again.

**royUK** · 10-13-2010, 07:30 AM

Did it ever work?

Please Login or Register  to view this content.

In the sheet

=cellformula(G6)

**jaw0001** · 10-13-2010, 07:44 AM

royUK:

It also works, but not the problem I am trying to solve. Your method uses a range directly. Mine passes a reference to a cell range (maybe the wrong terminology).

This may not be an appropriate venue for my question. Since it works sometimes I think I have problems like those suggested in prior post.

**royUK** · 10-13-2010, 07:45 AM

The code in your text file is not the same as that in the example workbook

**jaw0001** · 10-13-2010, 07:59 AM

royUK:

The text file is for the personal macros workbook. The workbook contains the function cellformula, which is part of the formula written to the cell when the macro is run. With the exception of the first two lines (used to provide a hotkey shortcut to the macro), I think the original post and the sample workbook and macros file are in agreement(?).