Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sheet Focus (Excel 2003)

    I have a workbook that will be viewed by several users. Users will look at several sheets. When the user click on a tab, I want the focus of that sheet to be on cell A1 no matter where it may have been when that sheet was last viewed.

    Also, I have 1 sheet that I use as a data list and depending on who viewed it last, it could be filtered so not all records are showing. I want it to show all records every time the file is opened no matter how it was left.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sheet Focus (Excel 2003)

    Hi There

    Open the Visual Basic Editor (Alt+F11)

    Double Click This Workbook and paste this code in:

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Range("A1").Select
    End Sub



    Now everytime the sheet tabs are clicked it reverts to cell A1...enjoy
    Jerry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sheet Focus (Excel 2003)

    In addition

    for the particular worksheet that contains the list. Right Mouse click the sheet tab and select View Code and paste this code into it:

    Private Sub Worksheet_Activate()
    Selection.AutoFilter Field:=1
    End Sub
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sheet Focus (Excel 2003)

    To expand on Jezza's code:

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Sh.Range("A1").Select
    If Sh.FilterMode = True Then
    Sh.ShowAllData
    End If
    End Sub

    This version will also turn off any filter. As Jezza noted, the code belongs in the ThisWorkbook module.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •