Excel 2003 screenupdating problem

**Softedge** · 02-20-2011, 09:16 PM

I have a macro I am running that was written on my Excel 2003 machine. The macro will not allow me to select a cell after I scroll the worksheet. If I select the cell without scrolling the sheet it allows the cell selection.

I have tried the same macro on my laptop that is running Excel 2007 and it works both ways.

I have Activesheet.UnProtect set and Application.Screenupdating = True set. The macro works on my laptop.

Is there a global setting in Excel that disables screenupdating that my macro is not overriding? What could be causing the problem?

All help would be appreciated. This one is baffling me.

Thanks,
Softedge

**6StringJazzer** · 02-20-2011, 10:14 PM

Welcome to the forum!

I think you're going to need to post your code. You can either attach your workbook (best option, but not if it includes private or sensitive data), or include the code in your post (please use code tags, see my signature for instructions).

**Softedge** · 02-20-2011, 10:20 PM

Thanks 6StringJazzer, here is the code.

Please Login or Register  to view this content.

**6StringJazzer** · 02-20-2011, 11:16 PM

What do you mean "not allowing" you to select? I get a message that the row is locked, but I pasted your code into a new blank workbook so all the rows are locked. But I get the feeling that's not what you meant. With this code I can freely scroll and select cells in both 2003 and 2007.

**Softedge** · 02-21-2011, 01:41 AM

Thanks for the reply 6StringJazzer

I agree, when I scroll the sheet I can select the cells and the inputbox in my excel 2003 will show the new cell selected, but the routine does not accept it, it returns the last active cell before the sheet was scrolled. I have uninstalled and reinstalled excel but there is no change. I am thinking that I must have set a registry setting while using excel that is preventing the macro from accepting the inputbox value after the screen is scrolled. It may be a screen updating problem or setting. I have been working with this routine all weekend. I am thinking in one iteration a registry value was set without my knowing. My question is, is there a global setting in excel that I may have inadvertently set.

Softedge

**Softedge** · 02-21-2011, 01:57 AM

6StringJazzer

I did the same thing you did and pasted the macro into a new sheet. Lo and behold it works no problem. I think I have a corruption in the sheet from the original workbook.

Do you know how I can get rid of the corruption? This is a workbook with many many links between 10 sheets that I do not want to have to rebuild.

Thanks for your help

Softedge

**6StringJazzer** · 02-21-2011, 08:03 AM

Once I had to resolve an apparent corruption in a large workbook. I opened a new workbook and created the right number of worksheets and renamed them. Then I just did a select-all, copy, and paste to copy all the content. You may need to do a find & replace on the formulas if you have external links that still point back to the old workbook (change [*] to null; hopefully you don't have any deliberate external references), but that's one shot for the whole workbook.

You may also need to use format painter to copy the formats. I have found that formats are not always carried along with a copy & paste when I expect them to be.

Do you have named ranges?

**Softedge** · 02-21-2011, 11:42 PM

6stringjazzer

I think that might be onerous, the sheets are highly formatted, full of formulas and have many links between sheets ( some through formulas and some created with vba routines). The workbook works on other computers so I may have to realize the limitations on my machine and just live with it. I hope that the workbook may reset itself while being used on the other machines and become workable on this machine.

I could go to Office 2010 but I find ribbon menus slower to get to commands and they rob so much screen real estate as to be less workable than 2003. Whoever the manager of the Office was that was convinced that this was a better way to go should have been fired one release ago. If ribbons were better, there would be no quick access toolbar and/or it would not be customizable. But I rant.

Regards,
Softedge