Hide Menu and tool bars

[gjadcock]

What VB code do I need to turn off menu bars and tool bars so the user just sees the top title bar when opening the file. Also is there any way to prevent the user selecting any cells on the front page, I want to ensure the only selectable field on the sheet is a Text box used for searching.

Thanks in advance

Graham

 

[Andrew Poulsom]

For hiding the Toolbars see:

http://www.ozgrid.com/VBA/ExcelWorkbookEvents.htm

For your second question right click the sheet tab and choose View Code. In the Properties window (press F4 if you can't see it) set the EnableSelection property to -4142 - xlNoSelection. Press Alt+F11 to return to your worksheet. The worksheet must be protected for the property to take effect.

 

[gjadcock]

Thanks Andrew, One problem the EnableSelection property seems to be re-setting itself to NoRestriction when I re open the file, any ideas??

Yes, I have un-protected the sheet, made the changes, protect the sheet and resaved before closing and re-opening to test.

 

[Andrew Poulsom]

You can set it again in the Workbook_Open event procedure, eg:

Private Sub Workbook_Open()
With Worksheets(1)
    .EnableSelection = xlNoSelection
End With
End Sub

 

[gjadcock]

Excellent, works fine now Thanks.

Just one more thing, on the sheet I have a textbox, is there anyway to have the text box selected (waiting for input, cursor flashing etc) the instant the user opens the file. At the moment someting is selected but you can't see it, if I type in anything I get the worksheet is protected message, which I would expect if I had physically selected a cell.

 

[gjadcock]

Andrew, Have sussed it using:

ActiveSheet.bldg_code_entry.Activate under Workbook_open

Thanks for your help on the other questions