Roberto Alves

Scripting and server based computing

Re-enable toolbars in Excel 2003

Sometimes you can be very surprised with the amount of irresponsible people doing some code as vbs scripts, programs or even simple excel macros that can negatively affect production environments.

Last week I got a very simple problem, that can be frustrating for a user. Some external partner of my company created a Excel spreadsheet with a macro that disable all toolbars and hide Excel menus. Basically, the macro cleans all Excel screen, transforming the spreadsheet into something similar to a Visual Basic program.

But after use this spreadsheet, all users start to report a problem with other Excel spreadsheets. All Excel toolbars disappeared. It means that the macro modification was made by a spreadsheet and saved the last Excel status in Windows profile.

In this case, I was forced into create a new Excel 2003 file with a macro to do the opposite, re-enabling all toolbars and menus again.

It is very simple:

  1. Create a new blankĀ  Excel spreadsheet.
  2. Go to Tools – Macro – Visual Basic Editor
  3. On your left side, in the Project panel, you need select ThisWorkbook
  4. In the left combo box you need select Workbook
  5. In the right combo box you need select Open
  6. Will appear the function that runs when this spreadsheet is opened
  7. Copy and paste inside the function the code below:

    On Error Resume Next
    Application.CommandBars(“Worksheet Menu Bar”).Enabled = True
    Application.CommandBars(“Standard”).Enabled = True
    Application.CommandBars(“Standard”).Enabled = True
    Application.CommandBars(“Cell”).Enabled = True
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    Application.CommandBars(“Toolbar List”).Enabled = True
    Application.DisplayFormulaBar = True
    ActiveWindow.DisplayHeadings = True
    Application.DisplayStatusBar = True
    ActiveWindow.DisplayHorizontalScrollBar = True
    ActiveWindow.DisplayVerticalScrollBar = True
    ActiveWindow.DisplayWorkbookTabs = True
    Application.CommandBars(“Standard”).Visible = True
    Application.CommandBars(“Formatting”).Enabled = True
    Application.CommandBars(“Formatting”).Visible = True
    Application.CommandBars(“Chart”).Enabled = True
    Application.CommandBars(“Forms”).Enabled = True
    Application.CommandBars(“Web”).Enabled = True
    Application.CommandBars(“Reviewing”).Enabled = True
    Application.CommandBars(“Visual Basic”).Enabled = True
    Application.CommandBars(“Drawing”).Enabled = True
    Application.CommandBars(“web”).Enabled = True
    Application.CommandBars(“Picture”).Enabled = True
    Application.CommandBars(“PivotTable”).Enabled = True
    Application.CommandBars(“CELL”).Enabled = True
    Application.CommandBars(“Visual Basic”).Enabled = True

  8. Save the spreadsheet with any name
  9. Ask to users open yours spreadsheet to solve the problem

Below you can see the Visual Basic macro editor after all configuration.

excel_enable_toobars1

Category: How to
Tag: ,

Your email address will not be published. Required fields are marked *

*