Hiding and restoring Excel Menu! So easy???? 
Author Message
 Hiding and restoring Excel Menu! So easy????

Those of you who use Visual Basic for Excel may have encountered the
following problem...

When you want to hide the Excel menu bar, you use the following line :
Application.CommandBars("WorkSheet Menu Bar").Enabled = False

And when you want to restore it :
Application.CommandBars("WorkSheet Menu Bar").Enabled = True

But there is a little problem...
If you want to use a "special command" in the menu bar, you have to
restore the menu (otherwise, you can't call this command using visual
basic). Once the menu
is restored, you have to hide it again, once the action you asked for
has been performed. And here appears the problem! I can't hide it again,
because if I do, Excell is too fast (!!!!) and can't perform the
previous command I asked for.

An example is shown here :

Sub Hide()
' Hide menu of Excel
' Macro recorded 17/2/99 by Yann
    Application.CommandBars("WorkSheet Menu Bar").Enabled = False
End Sub

Sub Gestion()
' Gestion Macro
' Macro recorded 17/2/99 by Yann
    With Application
        .ScreenUpdating = False
        .CommandBars("WorkSheet Menu Bar").Enabled = True
        .SendKeys ("%do")
        .SendKeys ("%w")
    End With
' This is to call the "Data Form" of Excel (english version of Excel)
' In order to try this, you have a to do a little table,
' with one cell in A8 (first line in A8)
End Sub

Sub Restore()
' Restore menu of Excel
' Macro recorded 17/2/99 by Yann
    Application.CommandBars("WorkSheet Menu Bar").Enabled = True
End Sub

This example is working well. But as you see, when I perform the Gestion
macro, I have to "unhide" the menu bar. And at the end of the Gestion
macro, the menu bar is still visible...
If I want to hide it again, I have to insert the following command :
    Application.CommandBars("WorkSheet Menu Bar").Enabled = True
Or I can call the Hide subroutine at the end of the Gestion subroutine.
In this case, it is not working, since Excell just "unhide" and "hide"
the menu bar... And can't
perform the action I asked for (Application.SendKeys ("%do") and
Application.SendKeys ("%w"))

So, do you have any idea to hide the menu once you have finished to
perform the Gestion subroutine?
In fact, it calls the "Data Form" of Excell, and once I have clicked on
Close, I would like to hide the menu bar again....

Thanks for your help,

Yann D.

Sun, 13 Jan 2002 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Hiding Minimize and Maximize/Restore buttons???

2. BUG(?): Menu not restoring keyboard focus

3. How to hide the menu bar?

4. Hiding menu bar and dock in OSX?

5. Want to hide a menu item in C4

6. Hiding menu in popup-window

7. Hiding and UnHiding menus / items at runtime

8. Hiding Menu Options (CW4b Legacy)

9. how to disable or hide Item in menu after login

10. Hiding Menu Items

11. Hiding 2003 App Frame menu options??

12. Hide Menu item in ABC


Powered by phpBB® Forum Software