Please Help!

[ethanfrink]

I have a barebones model that really doesn't include any content but does include some VBA (related to hiding and unhiding sheets upon opening). Long-story short, I figured out there is one magical (random) cell in the entire workbook that I can't touch. I mean, I am able to delete, modify, etc., but as soon as I do that, my model doesn't open. I can move the cell. I can even copy the contents of the cell. But if I overwrite the cell or change it, same result.

Here is the oddest part. Like I said, the cell is not being used for ANYTHING. When I click on the cell, it does not appear to be a named range. However, as soon as I click in the formula bar to edit it, the name "SUM" appears in the named range location! It isn't even being used for anything in my model, but I am now concerned about duplicate global named ranges somehow (if that even makes sense).

How can I fix this so I don't need to handle this random meaningless cell with kid gloves?!

THANK YOU!

 

[Jeffrey Mahoney]

Oooh, I love a good puzzle! How much of the code can you share? If I created a macro to get a list of your named ranges would you share that?

 

[ethanfrink]

When I look at my named ranges, it's not even listed! I have a few excel add ins for named ranges and it doesn't show up there either. I'm just trying to figure out how to get rid of it!

 

[Jeffrey Mahoney]

What sheet is it located? What cell address? Have you search the VBA for that cell reference?

This code will put a list of your named ranges starting with the activecell. Make sure it won't overwrite anything.

Sub ListNamedRanges()
  Dim wb As Workbook
  Dim NR As Name
  Dim X As Long
  
  Set wb = ThisWorkbook
  X = 0
  
  ActiveCell.Offset(X, 0).Value = "Name"
  ActiveCell.Offset(X, 1).Value = "Index"
  ActiveCell.Offset(X, 2).Value = "Refers TO"
  ActiveCell.Offset(X, 3).Value = "WB"
  For Each NR In wb.Names
    X = X + 1
    ActiveCell.Offset(X, 0).Value = NR.Name
    ActiveCell.Offset(X, 1).Value = NR.Index
    ActiveCell.Offset(X, 2).Value = "'" & NR.RefersTo
    ActiveCell.Offset(X, 3).Value = NR.ValidWorkbookParameter
    
  Next NR
    
End Sub

 

[Jeffrey Mahoney]

When you say your model doesn't open, do you mean the workbook won't open? You alter the cell, save it, close it, try re-open it and it says no?

Turn off your macros from running automatically. [Options - Trust Center - Trust Center Settings (button) - Macro Settings - Disable All Macros with Notification]

Alter the cell, save the file, close it, re-open it. See what happens.

 

[ethanfrink]

this lists tons of named ranged, but no SUM. there is absolutely nothing in the vba referring to "SUM" or the cell location. but when i strip out all VBA, the model does still open. trying to narrow it down by process of elimination.

 

[Jeffrey Mahoney]

I'm just trying offering things I would do. Try deleting the sheet the cell is on. I know it will delete all your formulas, so use a copy.

 

[ethanfrink]

this is the code that is somehow causing the problem.

Sub ExitWorkbook()
If Workbooks.Count < 2 Then
ThisWorkbook.Saved = True
SendKeys "%{F4}"
'trying to remove this SendKeys "%{F4}"
Application.Quit
Else
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True
End If
End Sub

 

[ethanfrink]

there isnt anything valuable on the sheet. removing the sheet inclusive of that magic cell is a problem. renaming the sheet isnt a problem. literally copying and pasting the meaningless formula from that cell INTO another cell and then deleting the original cell doesnt cause a problem. explain that one!

 

[PatOBrien198]

things to search for in your code:
"SUM" - yes, include the quotes
[SUM]
Range("Sum")
=SUM