Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    issue with opening workbook (excel 20000)

    HI all,

    I have been trying to get a piece of code that runs after a larger piece of code to bring the worksheets in the workbook to proper focus.
    Currently, i have a macro which save a workbook and creates a data file. after its process i have added various code to move the pages back to focus basically so
    every time i open a workbook i am see column A and cell a1. but some how this is not possible with anytype of code written.???? So I have question

    How do I write code that will bring every worksheet home to column a every time the workbooks are loaded up?

    thanks in advance for your time

  2. #2
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: issue with opening workbook (excel 20000)

    Hi Seaner,
    put the following macro in "Thisworkbook"

    Private Sub Workbook_Open()
    Dim wksSheet As Worksheet
    For Each wksSheet In ThisWorkbook.Worksheets
    Application.Goto Range("a1")
    Next
    End Sub


    You may also use "Range("a1").select". For the difference to "goto" see the VBA help for "goto".

    Regards

    Wolf

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: issue with opening workbook (excel 20000)

    hi wolf,
    should i include this with all the codes or somewhere else?

    so basically if I add this code to my macros modules, then everytime i open the saved workbook it will default to bring be to the home row and column?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: issue with opening workbook (excel 20000)

    <hr>so basically if I add this code to my macros modules<hr>

    No, It does not go in a :general module. As Wolf states you should:
    <hr>put the following macro in "Thisworkbook"<hr>

    go to VB (alt-F11)
    In the left "project explorer" pane
    dbl-click "thisWorkbook" in the workbook of interest
    Add the code to macro pane of this module when it opens

    Steve

  5. #5
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: issue with opening workbook (excel 20000)

    Seaner,

    sorry, there was a mistake in the code

    Private Sub Workbook_Open()
    Dim wksSheet As Worksheet
    For Each wksSheet In ThisWorkbook.Worksheets
    wksSheet.Activate
    Application.Goto Range("a1")
    Next
    End Sub

    Otherwise please follow what Steve said.

    This will now work for all sheets.

    Wolf

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: issue with opening workbook (excel 20000)

    Thanks,

Posting Permissions

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