PDA

View Full Version : Freezing Sheet Tabs - xl97



The Tamer
02-16-2005, 05:25 AM
Hi,

According to this post in MrExcel http://www.mrexcel.com/board2/viewtopic.php?t=99507, freezing sheet tabs is not possible. is that really so, can code not be written to always make sure that the tab of your choice is always, say, the left-most tab.?

Damo

Aaron Blood
02-16-2005, 07:32 AM
Code??? Just protect the workbook.


Edit: OHHHHhhh... you mean keep it at left like a frozen pane of cells...

Yeah, that sounds a little more difficult.

Typically, if I need to do somthing like that I just add a custom toolbar to the bottom of the workbook and put buttons on it to navigate to the appropriate sheets. Pretty much the same effect.

CBrine
02-16-2005, 07:37 AM
You can't freeze them, although you could most likely build code to set the worksheet index to 1, everytime they are changed. The other option would be to hide the tabs via code, using xlveryhidden.

Add this to the Thisworkbook level of the vbe to prevent a change to a different worksheet.


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
ActiveWorkbook.Worksheets(1).Select
End Sub

HTH
Cal

PS- I just did a test, and it stops them from changing to ws other then index 1, but index 1 can be changed by draggin the sheet tabs. I would suggest using a sheetname instead. Then protect the workbook to prevent changing of the name.


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
ActiveWorkbook.Worksheets("Sheet1").Select
End Sub

johnske
02-18-2005, 04:39 PM
Does this not do what I think you mean?


Option Explicit

Private Sub Workbook_Open()
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub :think:

The Tamer
02-19-2005, 06:22 AM
Thanks guys,

Just so you know, I wrote a thank you a couple 'o days ago, but I can't have submitted it properly. Anyhow... Aaron and Cbrine got my meaning correctly - I want to freeze only some of the sheet tabs - or even just one.

Cbrine, your code does is good enough for my needs, so thanks. And thanks everyone else for your input.

Damo