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

Daniel Cremin · Mar 28th, 2002, 06:20 AM

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

Daniel Cremin · Mar 28th, 2002, 08:55 AM

does ne one know anything that can help me with this?

Mark O'Brien · Mar 28th, 2002, 09:12 AM

-Have all Sheet tabs hidden

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

-No Row and Column Headers visible

Private Sub Workbook_BeforeClose(Cancel As Boolean)

  ActiveWindow.DisplayHeadings = True

End Sub



Private Sub Workbook_Open()

  ActiveWindow.DisplayHeadings = False

End Sub

-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

Daniel Cremin · Mar 28th, 2002, 12:12 PM

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.

Daniel Cremin · Mar 28th, 2002, 12:15 PM

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?

Mark O'Brien · Mar 28th, 2002, 12:20 PM

OK, thanks for pointing out the nametab in the options. I missed that before.

You're looking for something like this then:



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

Write another sub with the stuff set to True should reset the workbook.

Mark O'Brien · Mar 28th, 2002, 12:21 PM

Quote:

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:

 Application.DisplayAlerts = False

Are you sure you want to be able to see Excel at all?

Jack in the UK · Mar 29th, 2002, 06:49 AM

Mark

Out of intrest IE allow choisk opening of HTML so yes Excel could be totally hidden!

dk · Mar 29th, 2002, 07:08 AM

You can set the worksheet so that only unlocked cells are selectable by doing this:-

Sheets("Sheet name").EnableSelection = xlUnlockedCells

HTH,
D

Daniel Cremin · Mar 31st, 2002, 05:10 PM

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!

Mar 28th, 2002, 09:12 AM	#3
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	-Have all Sheet tabs hidden I don't think this is possible. It would make the workbook ridiculous. -No Row and Column Headers visible Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWindow.DisplayHeadings = True End Sub Private Sub Workbook_Open() ActiveWindow.DisplayHeadings = False End Sub -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 __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Mar 28th, 2002, 12:20 PM	#6
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	OK, thanks for pointing out the nametab in the options. I missed that before. You're looking for something like this then: 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 Write another sub with the stuff set to True should reset the workbook. __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Mar 29th, 2002, 06:49 AM	#8
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	Mark Out of intrest IE allow choisk opening of HTML so yes Excel could be totally hidden! __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 28th, 2002, 06:20 AM	#1
Daniel Cremin Board Regular Join Date: Feb 2002 Posts: 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

Mar 28th, 2002, 08:55 AM	#2
Daniel Cremin Board Regular Join Date: Feb 2002 Posts: 64	does ne one know anything that can help me with this?

Mar 28th, 2002, 12:12 PM	#4
Daniel Cremin Board Regular Join Date: Feb 2002 Posts: 64	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.

Mar 28th, 2002, 12:15 PM	#5
Daniel Cremin Board Regular Join Date: Feb 2002 Posts: 64	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?

Mar 29th, 2002, 07:08 AM	#9
dk MrExcel MVP Join Date: Feb 2002 Location: Sydney, Australia Posts: 2,908	You can set the worksheet so that only unlocked cells are selectable by doing this:- Sheets("Sheet name").EnableSelection = xlUnlockedCells HTH, D

Mar 31st, 2002, 05:10 PM	#10
Daniel Cremin Board Regular Join Date: Feb 2002 Posts: 64	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!