Please Help!

ethanfrink

New Member
Joined
Nov 21, 2014
Messages
19
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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?
 
Upvote 0
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!
 
Upvote 0
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.


Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top