We help IT Professionals succeed at work.

Excel VBA - How to get rid of the Minimize/Restore/Close at the far right of a menubar and the Excel icon at the far left?

In a custom menubar (set up as a commandbar with the menubar setting to true) I would like to get rid of the Excel icon at the far left that shows you Restore/Minimize/Close etc. as well as the _ [] X at the far right (the minimize, restore window, and close window options) at the far right.  

How can I do this?
Comment
Watch Question

RyanProject Engineer, Electrical
CERTIFIED EXPERT

Commented:
I don't think you can get rid of the control box for the Excel Application. Only for user forms.

If its in a custom menu, then just remove those icons from it? I don't see what icons on any default menus except under File.

You could override the Private Sub Workbook_BeforeClose(Cancel As Boolean) by setting cancel to true within that procedure.

RyanProject Engineer, Electrical
CERTIFIED EXPERT

Commented:
oh ya, if you set cancel = true, you need to put some kind of conditional there or you cannot close the workbook at all.

Heh, gives me an idea for a good prank.

Author

Commented:
The icons aren't under any menu, they're to the far right of the menu bar, to the right of the "Type a question for help" in the default Worksheet Menu Bar.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Try this: http://support.microsoft.com/kb/814562
Regards,
Rory
PS Note the comment about the availability of Application.hWnd in Excel 2002 and up.

Author

Commented:
Rory,

This was a great link.  However, it got rid of the control menu and the minimize/restore/close functionality above the toolbar, at the top left of the window bar and the top right of the window bar.

There is still a control menu and minimize/restore/close functionality right below that on the Worksheet Menu Bar.  I am attaching an image file that highglights this.

Possibly what you pointed me to could be used to get rid of these as well but I couldn't figure out how to get the hWnd of the window internal to Excel.
excel-control-menu-and-close-fun.gif
RyanProject Engineer, Electrical
CERTIFIED EXPERT

Commented:
Can I ask why you're wanting to get rid of those buttons? How do you intend to close the workbook then?

Author

Commented:
There's a custom Exit button on the toolbar.  Any changed data gets written to a special file, not saved in the spreadsheet.  I do not want users to do anything other than allowed by the custom toolbar.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
I think those are actually the control buttons for the workbook window (if you don't maximize the workbook, you should see those controls move to the title bar of the workbook window. From that I would guess you would have to disable them for every workbook opened. It might be easier for you to simply handle the workbook's events to write your data elsewhere rather than tring to disable all the controls.
Regards,
Rory
RyanProject Engineer, Electrical
CERTIFIED EXPERT
Commented:
Why not just put the custom exit procedures into the workbook_close event?

Just from experience, its better not to mess with normal procedures that a user would do, but to work with those. Other wise someone will have a dumb attack one day and flip out, not knowing how to close the workbook. Then its hard to tell what they'll do. (Like Ctrl+Alt+Del) or just restart the computer.

If need be, set some flags if certain things are done, then on workbook close, you can check for those. It's probably easier than what you're doing anyway.

Author

Commented:
After futilely trying to figure out what the hWnd of an internal Excel window is, I've decided to take both your advice and just let the user do whatever he wants and just handle the exit behavior.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.