Hiding Command Bars ??

[ssei1]

I want to hide the command bars for the instance of XCEL that I am using. DO NOT want to hide them for the application.

I have this working at the application level with Application.commandbars ... but the problem is that if I open up another instance of XCEL at the same time... there are no menus available.

I have not been able to get Thisworkbook.commandbars to work. I assume this would keep the hiding of the menus to the instance of the workbook I am using but this does not seem to work.

ANY IDEAS? Thanks in Advance !!!!!!

Rick

[RobDog888]

Ok, I got a little time tonight to look into this for you. About the
ThisWorkBook object from the help file:

The ThisWorkbook property returns the workbook where the
Visual Basic code is running. In most cases, this is the same as
the active workbook. However, if the Visual Basic code is part of
an add-in, the ThisWorkbook property won’t return the active
workbook. In this case, the active workbook is the workbook
calling the add-in, whereas the ThisWorkbook property returns
the add-in workbook.

If you’ll be creating an add-in from your Visual Basic code, you
should use the ThisWorkbook property to qualify any statement
that must be run on the workbook you compile into the add-in.

So you can see that the ThisWorkbook object will not work for
differenciating between commandbars of multiple instances of
workbooks at a time. I have a couple of ideas to test out, but
they will have to be for tomorrow.

[RobDog888]

Got to spend a little more time and I wrote a wrapper for the
activate and deactivate events of the workbooks collection. In
these events I am going to try to set the toolbars on and off
depending on which window is being displayed. Well we will see
if it works. Be back on Sunday with the code.

Later

[RobDog888]

Ok, the wrapper class works for instances created from within
my excel workbook. If you start another separate instance of
Excel, it will not work. So, this will not be a solution since we are
having to use reverse logic of taking away the commandbars only
when inside your instance of Excel and not the others. I am going
to try to see if I can do this from an VB/Excel Add-In?

Don't know if this will be ok for you?

[ssei1]

If I understand you... I would open my app and then from within kill the command bars. If the user trys to open another instance of XCEL from within the currently open instance the command bars will not show also?

If this is the case, this will work great for me because the user has no access to XCEL functions while within the app itself.

Would the user be able to get the command bars if they open another instance of XCEL from outside my app while my app is open?

Thanks Again,
Richard

[RobDog888]

Originally posted by ssei1
If I understand you... I would open my app and then from
within kill the command bars. If the user trys to open another
instance of XCEL from within the currently open instance the
command bars will not show also?

Yes, if the user opens the apps workbook, we have the ability to
either show or hide the command bars depending on the
workbook that is active, but only for within the instance of Excel.
If the user creates another instance of Excel from outside, it will
reflect whatever state the commandbars are in from the apps
workbooks. Is this all we need or more control?

[ssei1]

What I want to do is not influence the xcel application settings on the persons machine. The code below (HIDE_MENUS) works fine and hides any menus that show when my xcel application (called VPS) is opened. Unless I run another module (SHOW_MENUS) that I built before exiting VPS the next time I open XCEL to do something else... there are no menus.

I want to hide the menus and only impact VPS, which is the workbook instance that contains my VBA code.

Thanks, Rich

Sub Hide_Menus()

'****************************************************************************
'* THIS MODULE ANALYZES WHICH MENUS ARE SHOWING WHEN THE USER INITIATES VPS *
'* IT GOES THROUGH ALL POSSIBLE MENUBARS THAT COULD BE SHOWING. IF IT FINDS *
'* A MATCH IT WRITES THE MENU NAME TO "DLISTS" AND THEN MAKES THE MENU *
'* INVISIBLE. WHEN THE PROGRAM IS CLOSED. THE REVERSE PROCESS HAPPENS USING*
'* THE MODULE SHOW_MENUS *
'****************************************************************************
Dim Allbars As CommandBar
Dim I As Long


I = 0
MENUSHIDDEN = False

Application.Worksheets("DLISTS").Range("L1:L150").Clear 'CLEAR RANGE
DoEvents 'LET PROGRAM COMPLETE CLEAR


On Error Resume Next 'SWITCH ON RESUME NEXT


For Each Allbars In Application.CommandBars 'FOR EACH COMMANDBAR IN THE APPLICATION


If Allbars.Visible = True Then 'IF BAR IS VISIBLE

I = I + 1 'COUNT



Application.Worksheets("DLISTS").Cells(12).ROWS(I).Value = Allbars.NAME 'PUT NAME ON DLISTS

If Allbars.NAME = "Worksheet Menu Bar" Then
Allbars.Protection = msoBarNoProtection 'TAKE OFF PROTECTION
Allbars.Enabled = False 'DISABLE
Allbars.Visible = False 'MAKE INVISIBLE
Else



Allbars.Protection = msoBarNoProtection 'DITTO
Allbars.Enabled = False
Allbars.Visible = False
End If
End If


Next 'NEXT COMMANDBAR


Application.DisplayFormulaBar = False 'DON'T DISPLAY FORMULA BAR
On Error GoTo 0 'SWITCH ON ERROR OFF

MENUSHIDDEN = True


End Sub

[RobDog888]

Ok, I think I have another way. I can use some API calls to determie
if the focus has been lost, and if so I could re-display the
toolbars. This would work with my previous code to create the
Excel events. I will test out later today and post the results if it
works.