irrational problem
 

Hi

I have a problem which I'm sure (at least I hope) there's
an explanation for that I'm missing.

I have a workbook that when opened hides some commandbars.
There is a button on it which opens another workbook which
needs to be updated by the user and shows all the
commandbars (which are then hidden again when this
workbook is closed).
There is also a close button which allows the workbook to
be closed without quitting excel, in case they have other
workbooks open and can't get to it due to the abscence of
toolbars.

Anyway I've coded it so that when the user closes the
workbook by either the button or the x top right, if they
haven't clicked the button that opens this other workbook,
then a reminder message will be displayed asking them if
they want to. If they click yes then the other workbook is
opened.
When this happens the commandbars should reappear and they
do if the user has clicked the button on the sheet.
However, if they've clicked the x then the other workbook
opens ok, but only the formula bar is showing.
I've stepped through it all and it seems to execute but
doesn't acutally work, which is baffling.

Here's some of my code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
Select Case Sheet1.AdvControl 'boolean switch to see if
'button has been clicked
Case True
Call showCmdBars
Select Case Sheet1.CloseControl
Case False
Application.Quit
End Select
Case False
If MsgBox("open other workbook?", vbYesNo) = vbYes Then
Cancel = True
Sheet1.AdvControl = True
Call openAdv
Else
Select Case Sheet1.CloseControl
Case False
Call showCmdBars
Application.Quit
End Select
End If
End Select
End Sub

Sub openAdv()
Application.MoveAfterReturnDirection = xlToRight
On Error GoTo ErrHandler
Workbooks.Open "other workbook"
Call showCmdBars
end Sub



Private Sub cmdClose_Click()
Sheet1.CloseControl = True 'boolean switch
Select Case Sheet1.AdvControl
Case False
Sheet1.AdvControl = True
If MsgBox("open other workbook?", vbYesNo) = vbYes Then
Call openAdv
Exit Sub
ElseIf MsgBox("All data will be lost.", vbExclamation
+ vbOKCancel) = vbOK Then
Call showCmdBars
ThisWorkbook.Close
End If
Exit Sub
End If
Case True
If MsgBox("All data will be lost.", vbExclamation
+ vbOKCancel) = vbOK Then
Call showCmdBars
ThisWorkbook.Close
End If
Exit Sub
End Select
End Sub

Sub showCmdBars()
Application.CommandBars(1).Enabled = True
Application.CommandBars(2).Enabled = True
Application.CommandBars(3).Enabled = True
Application.CommandBars(4).Enabled = True
Application.DisplayFormulaBar = True
End Sub


Private Sub Workbook_Open()
Application.MoveAfterReturnDirection = xlDown
Sheet1.AdvControl = False
Sheet1.CloseControl = False
end sub