Can I prevent a sheet from moving within a workbook?

[Guest]

I want to know if there is a way to "freeze" a worksheet like you can
freeze/unfreeze panes on a worksheet. I have an Excel workbook that is
comprised of approximately fifty worksheets. I use the first worksheet as a
"lookup" worksheet, whereby the user can select a particular worksheet name
from a dropdown list, and then click a button that will take them directly to
the worksheet they picked. I want to be able to keep the "lookup" worksheet
static in its place, whereby it will always be on the left-most position of
the worksheet tabs at the bottom of the workbook.

 

[Vasant Nanavati]

AFAIK, no.

 

[Bob Phillips]

A bit of a fudge, but you could always add this to the ThisWorkbook code
module. I am assuming that the workhseet is called Summary.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Worksheets("Summary").Move before:=Worksheets(1)
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Vasant Nanavati]

I don't think that works, Bob; it makes the summary sheet active. I think
the OP wants the Summary sheet tab to be visible as the leftmost one on the
bottom even when Sheet99 is active.

Regards,

Vasant

 

[Bob Phillips]

You are correct, so this little mod might be better

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Worksheets("Summary").Move before:=Worksheets(1)
Application.EnableEvents = False
Sh.Activate
Application.EnableEvents = True
End Sub

Bob