Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm seeing a lot of weird things happening in some excel sheets I use.

  1. Some named ranges vanish
  2. Worksheet buttons 'forget' what Macro they should run
  3. Named ranges 'forget' what cells they pointed too

Most of the sheets are saved in xlsb

Has anybody else seen behaviour like this or have any idea what causes it?? Dave

share|improve this question
    
Have you checked for a virus? –  Xavierjazz Nov 23 '09 at 16:10
    
I have update from Office 2003 to 2007 and now I am having this issues as well. My code creates a new file in Office 2007, then copies a sheet from the 2003 upgraded file to the new file. So technically the file is new, but the sheet had been upgraded from a 2003 file. Without reason and without warning I note 2 named ranges get removed (there maybe more, but I have only stumbled on these 2). The named range is for a single cell and contains a formula. This happens more often than not when I create the new file, but its always the same to named ranges. Ideas? –  user25007 Jan 24 '11 at 1:22

2 Answers 2

I have found that named ranges in macros needs to be adjusted. MS Excel 2003 had this as a sample code

Columns("E:E").Select
ActiveWorkbook.Names.Add Name:="Temp2", RefersToR1C1:="='End-2-End'!C6"

MS Excel 2007 has this as a sample code.

Columns("E:E").Select
ActiveWorkbook.Names.Add Name:="Temp2", RefersToR1C1:="='End-2-End'!C5"

This simple change from C6 to C5 is the solution. Once those updates were made in the macros, Named Ranges worked again for me.

share|improve this answer
up vote 1 down vote accepted

I found the solution! It's a bug in Excel and there is a hot fix here Excel HotFix

share|improve this answer
    
Ouch, lots of stuff in there. Moral of the story, update your MS Office! –  mtone Feb 2 '10 at 22:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.