Method error on workbook close

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
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):

Code:
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
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

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
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.

Code:
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

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
Don't understand - all seems to be working now. All toolbars are back where they belong etc.

:confused:
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,563
Messages
5,765,119
Members
425,262
Latest member
sabry

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
Top