Method error on workbook close

[Glaswegian]

I have a workbook where I hide the normal toolbars and only show my custome toolbar. The workbook opens fine and only the custome toolbar shows. However, when I close the workbook I receive and error message
"Method 'visible' of object 'commandbar' failed." The code is fairly simple (or so I thought):

With Application
    .Caption = Empty
    .CommandBars("Worksheet Menu Bar").Enabled = True
    .CommandBars("Standard").Visible = True
    .CommandBars("Formatting").Visible = True
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .DisplayFormulaBar = True
    .CommandBars("BCS Toolbar").Enabled = False
End With

The opening code is

ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
Application.DisplayStatusBar = False
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False

If the 'visible' method works OK for the Formatting toolbar, I can't understand why it won't work for the Standard toolbar. Can anyone help?

Glaswegian
Excel 97 on NT4

 

[Tom Urtis]

There is no reference to the BCS Toolbar in your open event, but maybe because you don't get an error on open you have that toolbar visible all the time. Just for the heck of it, see if putting the line
On Error Resume Next
just before the With structure of your Close event alleviates the problem. It might be a small issue of Excel trying to do something it doesn't need to do, in this case disabling a command bar that doesn't need to be disabled.

 

[Glaswegian]

Tom

Many thanks - the workbook now opens without an error message. However (there's always a 'however'!), after closing the workbook, the Standard and Formatting toolbars have not re-appeared. Is this something to do with the differences between 'visible' and 'enabled'? Thought I'd better post the complete code for the opena nd close events this time. just in case.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Contents").Select

With ActiveSheet
    .CheckBox1 = False
    .CheckBox2 = False
    .CheckBox3 = False
    .CheckBox4 = False
    .CheckBox5 = False
    .CheckBox6 = False
    .CheckBox7 = False
    .CheckBox8 = False
    .CheckBox9 = False
    .CheckBox10 = False
    .CheckBox11 = False
    .CheckBox12 = False
    .CheckBox13 = False
    .CheckBox14 = False
    .CheckBox15 = False
    .CheckBox16 = False
    .CheckBox17 = False
    .CheckBox18 = False
    .CheckBox19 = False
    .CheckBox20 = False
    .CheckBox21 = False
    .CheckBox22 = False
    .CheckBox23 = False
    .CheckBox24 = False
    .CheckBox25 = False
    .CheckBox26 = False
    .CheckBox27 = False
    .CheckBox28 = False
    .CheckBox29 = False
    .CheckBox30 = False
    .CheckBox31 = False
    .CheckBox32 = False
    
End With

ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.Caption = ActiveWorkbook.Name

On Error Resume Next

With Application
    .Caption = Empty
    .CommandBars("Worksheet Menu Bar").Enabled = True
    .CommandBars("Standard").Visible = True
    .CommandBars("Formatting").Visible = True
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .DisplayFormulaBar = True
    .CommandBars("BCS Toolbar").Enabled = False
End With

Worksheets("Security Fee Instruction").Range("H18,E22,K22,L22,M22,M26,N26,H31,I33,E37,K37,L37,M37,M41,N41,G48").ClearContents
Worksheets("Security Fee Instruction").CheckBox1.Value = False

End Sub

Private Sub Workbook_Open()
Worksheets("Contents").Select
Range("A1").Select

Application.ScreenUpdating = False

With Application.CommandBars("BCS Toolbar")
    .Position = msoBarTop
    .Enabled = True
    .Visible = True
End With

'clear the printing checkboxes

With ActiveSheet
    .CheckBox1 = False
    .CheckBox2 = False
    .CheckBox3 = False
    .CheckBox4 = False
    .CheckBox5 = False
    .CheckBox6 = False
    .CheckBox7 = False
    .CheckBox8 = False
    .CheckBox9 = False
    .CheckBox10 = False
    .CheckBox11 = False
    .CheckBox12 = False
    .CheckBox13 = False
    .CheckBox14 = False
    .CheckBox15 = False
    .CheckBox16 = False
    .CheckBox17 = False
    .CheckBox18 = False
    .CheckBox19 = False
    .CheckBox20 = False
    .CheckBox21 = False
    .CheckBox22 = False
    .CheckBox23 = False
    .CheckBox24 = False
    .CheckBox25 = False
    .CheckBox26 = False
    .CheckBox27 = False
    .CheckBox28 = False
    .CheckBox29 = False
    .CheckBox30 = False
    .CheckBox31 = False
    .CheckBox32 = False
    
End With

ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
Application.DisplayStatusBar = False
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False

Application.Caption = "BUSINESS CREDIT SUBMISSION"

Worksheets("Security Fee Instruction").Range("H18,E22,K22,L22,M22,M26,N26,H31,I33,E37,K37,L37,M37,M41,N41,G48").ClearContents
Worksheets("Security Fee Instruction").CheckBox1.Value = False


End Sub

Sub clear_dfas_options()

   If ThisWorkbook.Name = "Amended BCS 1003 - Iain.xls" Then
      Worksheets("DFAS").Range("B16:B34").ClearContents
   End If
End Sub

   
Sub clear_dfas_continuation_options()

    If ThisWorkbook.Name = "Amended BCS 1003 - Iain.xls" Then
      Worksheets("DFAS Continuation").Range("B11:B41").ClearContents
   End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$5" Then Range("F10").Value = Date

End Sub


Glaswegian
Excel 97 on NT4

 

[Glaswegian]

Don't understand - all seems to be working now. All toolbars are back where they belong etc.