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]

this isn't going to be helpful to you in a vacuum. basically when the workbook is open a disclaimer is shown. if it is accepted, a variable is set (thereby making the workbook unsaved). if after the disclaimer is unloaded, if the excel file is "unsaved" (i.e. the acceptance wasnt clicked and the variable not set), then it shuts down. somehow that cell (or its contents) is interfering with my variable set, even though one has nothing to do with another.

 

[ethanfrink]

"If Me.PrintBox4.Value = True And SheetNames <> "" Then
SheetNames = SheetNames & "," & "Sum"
ElseIf PrintBox4.Value = True And SheetNames = "" Then
SheetNames = "Sum"
End If"

 

[ethanfrink]

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

removing the print box reference to this still leaves me with the problem

 

[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

I think this is the only other relevant part of the code. this is where the variable is set if the disclaimer is accepted.

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

 

[ethanfrink]

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.

the model will open initially but wont show the disclaimer (or any other worksheet). once the vba is enabled, the dislaimer is shown, but even if it is accepted, the model exits.

 

[Jeffrey Mahoney]

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.

 

[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.

just trying to understand the intent. i have no doubt that removing/commenting out that code will prevent the workbook from closing. but it's always worked fine (closing the workbook only when it is supposed to).

1. I am confused how/why the SUM name is somehow associated with that cell (but not before you click it)
2. I don't understand how the SUM name follows the formula contents and not the cell itself
3. I don't understand why the SUM name is somehow interacting with my VBA instructions.

 

[Jeffrey Mahoney]

Can you please post the Workbook_Open() SUB.

 

[ethanfrink]

Can you please post the Workbook_Open() SUB.

Private Sub Workbook_Open()
formDisclaimer.Show
'CheckPassword 'enable this for password protection
If Me.Saved Then
ExitWorkbook
Exit Sub
Else
Application.ScreenUpdating = False
UnprotectWorkbook
Dim ws As Excel.Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = True
Next
Application.Goto "TOC"
DefineStartCell
HideSheets
ProtectWorkbook
Application.ScreenUpdating = True
Exit Sub
End If
End Sub

 

[Jeffrey Mahoney]

1. I am confused how/why the SUM name is somehow associated with that cell (but not before you click it)2. I don't understand how the SUM name follows the formula contents and not the cell itself
3. I don't understand why the SUM name is somehow interacting with my VBA instructions.

That's what I want to track. The Workbook_Open SUB starts the whole thing. The variable "SheetNames" is related I believe. We have to find out how the cell gets the change.