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!

 

[ethanfrink]

SendKeys "%{F4}" is equivalent to thisworkbook.close. SendKeys is outdated.

Try commenting out that code and see what happens.

Search for ExitWorkbook throughout the entire VBA and show the calling code. My bet is it's in the "ThisWorkbook" module in the Workbook_BeforeClose sub.

thanks for the sendkeys pointer, will implement

 

[Jeffrey Mahoney]

Application.Goto "TOC"
DefineStartCell
HideSheets
ProtectWorkbook

TOC is a named range. Where is that. Use F5 (goto)

DefineStartCell is a SUB. What's in that?
HideSheets is a SUB. What's in that?
ProtectWorkbook is a SUB. What's in that?

 

[ethanfrink]

"TOC" is a defined name, which I am aware of and has no interactivity with the random SUM cell. definestartcell was the first code i posted. hidesheets literally hides all the sheets except for the disclaimer. protectworkbook literally just protects the workbook (as in the excel protection offered under the "Review" tab)

TOC is a named range. Where is that. Use F5 (goto)

DefineStartCell is a SUB. What's in that?
HideSheets is a SUB. What's in that?
ProtectWorkbook is a SUB. What's in that?

 

[ethanfrink]

TOC is a named range. Where is that. Use F5 (goto)

DefineStartCell is a SUB. What's in that?
HideSheets is a SUB. What's in that?
ProtectWorkbook is a SUB. What's in that?

Sub DefineStartCell()
'MsgBox "DefineStartCell" 'test
If ActiveWindow.SelectedSheets.Count > 1 Then
Sheets("TOC").Select
Application.Goto "TOC"
Else
''
End If
Set StartWorksheet = ActiveSheet
Set StartCell = Selection
End Sub

 

[Jeffrey Mahoney]

I'm drawing blanks. I'd have to see it in action. Do you know how to step through the macro? You can put your cursor inside the Workbook_open sub and press F8. Each press of F8 is a step. You can see the code as it works. Maybe it will lead you to the part where it interacts with the cell. My guess is the value of SheetNames is being put into the cell.

Maybe you could give the SUM cell an actual named range, put it on an unused sheet and forget about it.

 

[Jeffrey Mahoney]

The disclaimer must be a Form. Look at the code inside the Form.

 

[ethanfrink]

I'm drawing blanks. I'd have to see it in action. Do you know how to step through the macro? You can put your cursor inside the Workbook_open sub and press F8. Each press of F8 is a step. You can see the code as it works. Maybe it will lead you to the part where it interacts with the cell. My guess is the value of SheetNames is being put into the cell.

Maybe you could give the SUM cell an actual named range, put it on an unused sheet and forget about it.

it's worked flawlessly for years. only encountering an issue here because i've been trimming down the workbook and deleted that now "magical" cell. i can leave that cell alone and my world is perfect. but i hate open questions like that as I'm sure you do as well!

ultimately, i think it's as simple as somehow figuring out the concept of the "Semi" named range. it has to be something other people encounter but i have no idea how to search for it. i'm confident that once i get rid of that, all will be right with the world. thank you for your time today!

 

[ethanfrink]

The disclaimer must be a Form. Look at the code inside the Form.

i did, truly nothing suspect as far as i can tell! certainly no interactivity with that cell. have you ever heard of vba code somehow getting corrupted? i've seen these "cleaners" for sale, is there ever a need for that?

 

[Jeffrey Mahoney]

In the way past I had a need for cleaners. Those kinds of things don't plague Excel much any more. There are ways to reduce file size and get rid of the clutter using Excel itself.

I'm still intrigued at this pseudo named range. Excuse me while I stand over your shoulder and take a look....