Freezing Sheet Tabs - xl97

[The Tamer]

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]

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]

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]

Does this not do what I think you mean?

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

[The Tamer]

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