sheet without the excel menus

[Pierre via OfficeKB.com]

Hi,

How can i show a particular sheet from a userform without:

- the excel menu above ?

- the toolbars not visible ?

Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200510/1

[Leith Ross]

Hello Pierre,

This macro will remove all the Toolbars that are visble as well as the Menu bar. Remember, once the macro is run, the only way to restore the Menu is through the code. So be careful.

Please Login or Register  to view this content.

[Pierre via OfficeKB.com]

Hi Leith,

thanks for your input, i really appreciate it.
Can you please give a little more explanation on how your code works please?
Here's how i see it...

> Dim I As Long 'counter
>
> With Excel.CommandBars
> For I = 1 To .Count ' for each commandbar
> If .Item(I).Visble = True Then ' if the commandbar is visible then
> If .Item(I).Name = "Worksheet Menu Bar" Then ' appearently the main manu of excel is also a commandbar ?
> .Item(I).Enabled = False 'disable the main menu
> Else 'else
> .Item(I).Visble = False ' make the commandbar invisible
> End If
> End If
> Next I
> End With
>
> End Sub

The problem is that if you execute this code you do not know which bars were
visible when you started executing this code.
Could i just make the main menu invisible en then set the view to full screen
to make the command bars disappear? if this is possible, do you have the code
to do that and the code to make the mainmenu visible again and return to non
full screen view ?

thanks for your input !
Pierre



Leith Ross wrote:
>Hello Pierre,
>
>This macro will remove all the Toolbars that are visble as well as the
>Menu bar. Remember, once the macro is run, the only way to restore the
>Menu is through the code. So be careful.
>
>Code:
>--------------------
> Sub RemoveToolBars()
>
> Dim I As Long
>
> With Excel.CommandBars
> For I = 1 To .Count
> If .Item(I).Visble = True Then
> If .Item(I).Name = "Worksheet Menu Bar" Then
> .Item(I).Enabled = False
> Else
> .Item(I).Visble = False
> End If
> End If
> Next I
> End With
>
> End Sub
>
>--------------------
>


--
Message posted via http://www.officekb.com

[Leith Ross]

Hello Pierre,

I thought about that when I wrote the code and decided to stay with what you posted. This code keeps track of the visible toolbars in an array. You can then re-enable the original toolbars. The Worksheet Menu is left enabled.

Paste this code into the General Declarations section of any Worksheet. You can then call the macro.

Please Login or Register  to view this content.

Sincerly,
Leith Ross

[Norman Jones]

Hi Leith,

> Paste this code into the General Declarations section of any
> Worksheet.
> You can then call the macro.

Out of curiosity, why do you suggest that the code should be placed in a
worksheet code module?

---
Regards,
Norman



"Leith Ross" <[email protected]> wrote
in message news:[email protected]...
>
> Hello Pierre,
>
> I thought about that when I wrote the code and decided to stay with
> what you posted. This code keeps track of the visible toolbars in an
> array. You can then re-enable the original toolbars. The Worksheet Menu
> is left enabled.
>
> Paste this code into the General Declarations section of any Worksheet.
> You can then call the macro.
>
>
> Code:
> --------------------
> Public Toolbars As Variant
> _________________________________________________________________
>
> Public Sub RemoveToolBars()
>
> Dim CB()
> Dim I As Long
> Dim N As Long
>
> 'Find all the visible toolbars index numbers and save them
> For I = 1 To Excel.CommandBars.Count
> If CommandBars(I).Visible = True Then
> N = N + 1
> ReDim Preserve CB(N)
> CB(N) = I
> End If
> Next I
>
> 'Disable the toolbars except for the Worksheet Menu
> For I = 1 To N
> If CB(I) <> "Worksheet Menu Bar" Then
> Excel.CommandBars(CB(I)).Enabled = False
> End If
> Next I
>
> CB(0) = N
>
> Toolbars = CB()
>
> End Sub
> _________________________________________________________________
>
> Public Sub RestoreToolBars()
>
> Dim I As Long
>
> For I = 1 To Toolbars(0)
> CommandBars(Toolbars(I)).Enabled = True
> Next I
>
> End Sub
>
> --------------------
>
>
> Sincerly,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile:
> http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=478250
>

[Leith Ross]

Hello Norman,

You could just as easily place the code into a VBA Module. Since I don't know Pierre's level of experience with VBA, he probably is familiar with using code at the Worksheet level. No other reason.

Sincerely,
Leith Ross