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

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

--------------------


--
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

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

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

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" wrote
in message ...

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

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


--
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