Can I prevent a sheet from moving within a workbook?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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)
 
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
 
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
 
Back
Top