Calc: porting macro from Excel 7/97

[wcpa9272@aol.com]

I am porting a 1998 Excel 7/97 file into Calc that has macros run at opening to set up a custom menu bar
The code is failing as shown below. A Fix suggestion will be appreciated. Everything works EXCEPT things to do with the menu bar.

THE ERROR MESSAGE ON Sub Auto_Open is..

BASIC runtime error.
'1'
An exception occurred
Type: com.sun.star.uno.DeploymentException
Message: component context fails to supply singleton com.sun.star.ui.theModuleUIConfigurationManagerSupplier of type com.sun.star.ui.XModuleUIConfigurationManagerSupplier.


THE MACRO BEING RUN

Code: Select all

'-----------------------------------------------------------------------------'
Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1


Sub Auto_Open()
' SetUp Menus Removes Entire Standard Excel Menu Bar
    Call Menu_SetUp
' Initialize ListBoxes With Defaults Sets Up Custom Menu With Defaults
    Call SetUpList
' Initialize Banner
    Call Banner_Default
' Activate Sheet Makes Sheet "Calculation" Visible
    Call Sheet_Activate
' Turn on screen updates - Updates Screen With Click / Input
   Application.ScreenUpdating = True
' Open Main StartUp Dialog Sheet Displays Custom Help Message
   ThisWorkbook.DialogSheets("DlgMainHelp").Show'
End Sub

Sub Menu_SetUp()
  With Application
ERROR ON LINE BELOW
        .CommandBars("Standard").Visible = False   
        .CommandBars("Formatting").Visible = False
        .CommandBars("Chart").Visible = False
        .CommandBars("Control Toolbox").Visible = False
        .CommandBars("Drawing").Visible = False
        .CommandBars("External Data").Visible = False
        .CommandBars("Forms").Visible = False
        .CommandBars("Picture").Visible = False
        .CommandBars("PivotTable").Visible = False
        .CommandBars("Reviewing").Visible = False
        .CommandBars("Visual Basic").Visible = False
        .CommandBars("Web").Visible = False
        .CommandBars("WordArt").Visible = False
        .DisplayFormulaBar = False
        .DisplayRecentFiles = False
    End With

[JeJe]

Put a ' in front of Call Menu_SetUp or delete it and the Menu_SetUp sub.

You can customise the document's menus with Tools menu/customize. The menu items are different from the Excel ones - trying to show the WordArt one makes no sense for example as Calc doesn't have WordArt...

[JeJe]

Or rather set it to not visible.

You can get a clean screen view without any menus with full screen mode (press Ctrl + shift + j)
If you want a macro to do that the macro record function (if turned on in the options) will help you generate one (tools menu/macros/record macro)


You can also hide the layout manager (includes menus and toolbars) without changing to full screen mode with this line of code

Code: Select all

thiscomponent.currentcontroller.frame.layoutmanager.setvisible(false)

And bring it back with this line

Code: Select all

thiscomponent.currentcontroller.frame.layoutmanager.setvisible(true)

[wcpa9272@aol.com]

The whole point of the code is to make the standard menu bar invisible so a later sub an add a custom menu. The ? really is — why doesn’t the line with .Command……….. work

[Villeroy]

For the same reason why Excel can not run

Code: Select all

thiscomponent.currentcontroller.frame.layoutmanager.setvisible(false)

You can configure document/template specific menues and toolbars. There is no need to generate them programatically.

[wcpa9272@aol.com]

This sheet is used by others who don’t get involved in anything but entering data from drop-down boxes and sliders. The custom menus walk them through the operation

That’s why the auto_open runs

[JeJe]

Why does that stop you just customizing the document via Tools/customize? If you do that it will open as you want it already without having to run any macro.

[JeJe]

To hide the standard toolbar

Code: Select all

lm = thiscomponent.currentcontroller.frame.layoutmanager
lm.hideElement("private:resource/toolbar/standardbar")
'lm.showElement("private:resource/toolbar/standardbar")

[Villeroy]

This stores your customized menu in the document or template. When you store it in a template, each document derived from that template will have this menu. The same applies to toolbars and context menues but not to shortcuts. Different shortcuts for different documents would be too confusing.

Customisation_Scope.png (62.6 KiB) Viewed 802 times