Hiding menu bar in excel 2

[henkus]

Iwould like to be able to hide or lock up the menu bar in my exel 98 application through an Auto open macro and re-establish again with Auto close. I've managed to do this with all the tool bars but can't work out how to do it with the menu bar. can it be done?

 

[acron]

Try :

Application.CommandBars _
           ("Worksheet Menu Bar").Enabled = False

and use = True to reset.

AC

 

[JESTAR]

Hi, put this in your main module.

Sub Start()
ClearAll
UserForm.Show
Unload UserForm
End Sub

Sub ClearAll()
Application.ScreenUpdating = False
ClearWorkSheet
ClearActiveWindow
ClearApplicationControls
Application.ScreenUpdating = True
End Sub

Sub ResetAll()
Application.ScreenUpdating = False
ResetWorkSheet
ResetActiveWindow
ResetApplicationControls
Application.ScreenUpdating = True
End Sub

Sub ClearWorkSheet()
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.WindowState = xlMaximized
' xlMaximized or xlNormal
End With

'ActiveSheet.SetBackgroundPicture ("C:\My Documents\My Pictures\Yosemite.jpg&quot
'Just an example to show how to set a picture on the background, otherwise it will just be white.
End Sub

Sub ResetWorkSheet()
With ActiveWindow
.DisplayGridlines = True
.DisplayHeadings = True
.WindowState = xlMaximized
' xlMaximized or xlNormal
End With

'ActiveSheet.SetBackgroundPicture ("&quot
'Disables the background picture when reset
End Sub

Sub ClearActiveWindow()
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With

' Application.DisplayScrollBars = False
' Turns scrollbars off for all workbooks
End Sub

Sub ResetActiveWindow()
' Resets the scrollbars for all workbooks
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
End With
End Sub

Sub ClearApplicationControls()
Dim OneBar As CommandBar

' First the normal screen
With Application
.DisplayFullScreen = False
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With

' Hide all Command Bars
On Error Resume Next
For Each OneBar In CommandBars
OneBar.Visible = False
Next
On Error GoTo 0

' Now viewing full screen
With Application
.DisplayFullScreen = True
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With

' Hide all Command Bars
On Error Resume Next
For Each OneBar In CommandBars
OneBar.Visible = False
Next
On Error GoTo 0

' Disable the Menu Bar only required once
CommandBars("Worksheet Menu Bar&quot.Enabled = False
End Sub

Sub ResetApplicationControls()
' First viewing full screen
With Application
.DisplayFullScreen = True
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With

' Turn on main CommandBars
CommandBars("Standard&quot.Visible = True
CommandBars("Formatting&quot.Visible = True

' Now the normal screen
With Application
.DisplayFullScreen = False
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With

' Turn on main CommandBars
CommandBars("Standard&quot.Visible = True
CommandBars("Formatting&quot.Visible = True

' Re-enable the Menu Bar
CommandBars("Worksheet Menu Bar&quot.Enabled = True
End Sub


And in the button that exits the UserForm and goes back to Excel...

Private Sub BtnExit_Click()
'Runs procedures that reset Excel toolbars and exit the interface.
' Also saves the workbook and exits Excel completely.

Me.Hide
ResetAll
'ThisWorkbook.Save
'Application.Quit
End Sub


Okay, couple of things to note down here. In BtnExit_Click, ThisWorkbook.Save and Application.Quit are commented out. Uncommenting them will close Excel and save the workbook.

Also, you will need to disable the QueryClose (the little X in the top right corner). Use the following code in the code for the form that has the Exit button above in it...

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If Cancel <> 1 Then
Cancel = 1
End If
End Sub

It won't make the button disappear, but will disable it. This is very important, because if someone opens the form, Excel is cleared, but if they don't press the Exit button to exit, then Excel remains cleared. Only pressing Exit button will reses Excel.

I am assuming that Sub_Start() is the procedure that is assigned to your button in Excel that runs the form.

Comment out ClearAll in Sub_Start() and Excel won't clear. Useful for testing.

 

[JESTAR]

Hi again

Some more...

In ThisWorkbook....

Option Explicit

ThisWorkbook_Open()
Start
End Sub

That will run the UserForm as soon as the file is opened. The problem you may have is that if you use the code to exit and save Excel instead of returing to the worksheets upon clicking the Exit button, then you won't be able to access the worksheets. The best way to get around this is to have a password.

Get a new form, put a textbox (TextWord), a button (ButtonEnter) and another button (ButtonCancel).

Option Explicit

Dim Pass as Integer

Private Sub TextWord_Change()
PasswordChar = &quot;*&quot;
End Sub

Private Sub ButtonEnter_Click()

If Pass > 3 Then
Me.Hide
forminterface.Show
End If
If TextWord = &quot;YOURPASSWORD&quot; Then
Me.Hide
Else: MsgBox &quot;error&quot;
Pass = Pass + 1
End If
End Sub

Private Sub ButtonCancel_Click()
Me.Hide
UserForm.Show
End Sub

Private Sub UserForm_Initialize()
Pass = 0
TextWord = &quot;&quot;
TextWord.SetFocus
ButtonEnter.Default = True
End Sub



Where it says YOURPASSWORD, just put your own password. Remember case sensitivity. The password will be displayed in the TextWord textbox as *'s because of the PasswordChar = &quot;*&quot;.

Upon clicking Cancel, you are returned to the userform from where you came.

If you enter the password wrong more than 3 times, the form closes.

 

[henkus]

Hey thanks it's so easy when you know how!

henkus.

 

[DaleWatson123321]

henkus,

After such a LENGTHY solution, it seems clear that the contributor deserves the &quot;proper recognition&quot; - which is one of those &quot;STARS&quot;.

As I can see from your &quot;profile&quot;, you're relatively new to Tek-Tips, and therefore not yet aware of this &quot;proper&quot; practice of showing recognition and appreciation for a useful posting. I too was unaware of this for the first while.

The &quot;issuing of a &quot;STAR&quot; is really very simple. Just click on the &quot;Click here to mark this post as a helpful or expert post!&quot; - located in the lower-left-corner of the contributor's posting.

I'm told these STARS also serve as &quot;beacons&quot; for other Tek-Tips &quot;browsers&quot; who are &quot;on the lookout&quot; for useful tips and solutions.

But, most importantly, is does &quot;properly&quot; show appreciation for those who take time out of their busy schedules to help Tek-Tippers &quot;in distress&quot;.

I need to ask that you PLEASE do NOT consider this &quot;reminder&quot; as being worthy of a STAR. I want to EARN stars in the normal way. Thanks.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

My apologies,

I was OBVIOUSLY TOOOOOO quick.

henkus, my apologies for not &quot;counting on you&quot; to notice the proper method of showing appreciation.

THANKS !!!!

...Dale Watson dwatson@bsi.gov.mb.ca

 

[JESTAR]

Dale, how do I put that information I posted in an FAQ? I try time and time again to do so, but get an error message when trying to submit.

Is it too much info to put in an FAQ?

 

[DaleWatson123321]

JESTAR,

Seeing as you tried to submit the FAQ, you must have used the same method I used, and I'm not aware that there is more than the one method.

The only thing I can think of, is to make an enquiry with the &quot;top brass&quot; at Tek-Tips. As I haven't (yet) done so, I don't know what type of success you'll have. But, given the overall EXCELLENT management of Tek-Tips, I would expect that someone &quot;at the top&quot; will be prepared to &quot;help you out&quot; - because, after all, you are &quot;helping them out&quot; with your contributions.

The one email address which I could locate within the Tek-Tips screens is the following:

advertising@tecumsehgroup.com

While it is the &quot;advertising&quot; department, they should be able to re-direct your email to the proper person who can help.

I seem to recall another email address from awhile back. I think it's the one everyone gets after becoming a member of Tek-Tips. If my memory serves me correctly, it was from:

dmurphy@tecumsehgroup.com

I think he is THE &quot;top guy&quot;, and that his name is Dave Murphy. I'm not exactly sure of his title, but it's probably something like &quot;President and CEO&quot;.

Again, if it's an accurate email address, you should expect results.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JESTAR]

Cheers Dale. Will do that. This is the error message I get anyway..........


Error Occurred While Processing Request
Error Diagnostic Information
Just in time compilation error

Invalid parser construct found on line 150 at position 186. ColdFusion was looking at the following text:

'
Invalid expression format. The usual cause is an error in the expression structure.
The last successfully parsed CFML construct was dynamic text (a CFML expression) occupying document position (150:124) to (150:155).

The specific sequence of files included or processed is:
D:\webs\TEK-TIPS\submitfaq.cfm


Date/Time: 03/02/02 06:12:47
Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98; Win 9x 4.90)
Remote Address: 62.252.32.4
HTTP Referrer: faq707-0 String: CFID=47297168&CFTOKEN=59858445




Please inform the site administrator that this error has occurred (be sure to include the contents of this page in your message to the administrator).



I emailed that emial address (that didn't appear here) and no reply.