I have a workbook where I want to hide all menu bars and toolbars for
the user when it’s opened. On the other hand, when the workbook is
closed (with a ‘close’ button on the sheet) I’d like to turn everything
back on.
The code is doing fine on the workbook_open event. Everything is
hidden, as I want. However, on the closebutton_click event everything
works fine except for the following 3 lines:

Application.displaystatusbar = true
Application.displayscrollbars = true
Application.displayformulabar = true

For all 3 I get the similar message:

Run-time error 1004 method ‘displayscrollbars’ of object ‘_application’
failed

However, when I write those lines in a separate sub and run it
manually, they work fine. If I try to call this sub when closing the
workbook the error appears again.

Does anyone know what’s happening?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Sounds like you are running the commands without a workbook being open. Run
them, then close the workbook.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"marecq" wrote in message
...
I have a workbook where I want to hide all menu bars and toolbars for
the user when it's opened. On the other hand, when the workbook is
closed (with a 'close' button on the sheet) I'd like to turn everything
back on.
The code is doing fine on the workbook_open event. Everything is
hidden, as I want. However, on the closebutton_click event everything
works fine except for the following 3 lines:

Application.displaystatusbar = true
Application.displayscrollbars = true
Application.displayformulabar = true

For all 3 I get the similar message:

Run-time error 1004 method 'displayscrollbars' of object '_application'
failed

However, when I write those lines in a separate sub and run it
manually, they work fine. If I try to call this sub when closing the
workbook the error appears again.

Does anyone know what's happening?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Workbook is open, but yesterday evening I found a possible solution to
avoid the problem. I noticed that the code doesn’t work as long as one
of the buttons on the worksheet has the focus (?). So a simple solution
is to start the code with something like:
Cells(1,1).select

Now it’s working fine (though I still don’t know why it didn’t work the
other way)



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/