How do i unhide sheet tabs, scroll bars, row and column head

Daniel Cremin

Board Regular
Joined
Feb 23, 2002
Messages
64
Hi im in really really big trouble with a macro i always thought would be easy. I need to be able to apply the following to always happen to all sheets when i open a workbook in Excel:

-Have all Sheet tabs hidden
-No Row and Column Headers visible
-Have all sheet protected so that only unlocked cells can be selected.

When the system closes and opens each times it needs to be like this! Please Please Please help me
Thanks very very much

Daniel
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
-Have all Sheet tabs hidden

I don't think this is possible. It would make the workbook ridiculous.

-No Row and Column Headers visible

<pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWindow.DisplayHeadings = True
End Sub

Private Sub Workbook_Open()
ActiveWindow.DisplayHeadings = False
End Sub</pre>

-Have all sheet protected so that only unlocked cells can be selected.

No practical way to do this and I can't see the point. Why shouldn't the user be able to select locked cells if they can't change the contents?
You can protect the sheet manually, but if you need code to do this use the "search" on this website to find it.

Why do you need to do this? I used to asked for stupid crap like this from my boss when he was too cheap to purchase a GUI package. I get the feeling you're up against something similar.

HTH
 
Upvote 0
What i basically want is an auto_open macro that brings up the Main interface worksheet of the spreadsheet, and has the sheet tabs set to not be visible (you know when you can go tools options sheet tabs and click it to not be visible). Also i want no toolbars visible apart from the basic worksheet menu bar (file, edit, view, etc).

A second macro will if what the user types into a specified cell is correct (an If... Then statement) unlock the system by running a macro that makes the standard, formatting, forms, and drawing toolbars visible on the screen and puts up the sheet tabs again.
 
Upvote 0
Oh yeah is it possible to have Excel save a workbook then save it as a pre-named workbook e.g. "year10archive", without having the Are you sure you want to overwrite the existing workbook message box popping up?
 
Upvote 0
OK, thanks for pointing out the nametab in the options. I missed that before.

You're looking for something like this then:

<pre>
Public Sub main()

Dim cmdbar As CommandBar

For Each cmdbar In Application.CommandBars
cmdbar.Enabled = False
Next

With ActiveWindow
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With

End Sub</pre>

Write another sub with the stuff set to True should reset the workbook.
 
Upvote 0
On 2002-03-28 11:15, Daniel Cremin wrote:
Oh yeah is it possible to have Excel save a workbook then save it as a pre-named workbook e.g. "year10archive", without having the Are you sure you want to overwrite the existing workbook message box popping up?

Use:

<pre> Application.DisplayAlerts = False</pre>

Are you sure you want to be able to see Excel at all? :biggrin:
 
Upvote 0
You can set the worksheet so that only unlocked cells are selectable by doing this:-

Sheets("Sheet name").EnableSelection = xlUnlockedCells

HTH,
D
 
Upvote 0
cheers mate for the last bit of code there! It means instead of having to go to every worksheet and then do the protection i can just set each worksheet to be like that!
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top