Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workbook_Open() (excel 97)

    I have code in Workbook_Open() in ThisWorkbook section of various files that we use. A common thing that I get it to do is to resize the worksheet to fit to the users screen, but some files do more complicated things when you open them.

    When I open the file by double clicking in windows explorer or by File>Open the Workbook_Open() runs normally.

    But I also open these files from another workbook. I have Worksheet_Change(ByVal Target As Excel.Range) in looking for a change in a cell in this workbook
    When a user types FILENAME in that cell and enters it opens FILENAME.xls but doesn

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

    Re: Workbook_Open() (excel 97)

    The Workbook_Open event procedure should be executed when you open a workbook in code (unless EnableEvents is False, but you turn it on before opening the workbook). Could you test what happens in a very simple situation?

    Workbook A has

    Private Sub Workbook_Open()
    Beep
    End Sub

    Workbook B has a macro that opens Workbook A. Do you hear a beep if you run this macro?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() (excel 97)

    Are you absolutely sure the Open event does not fire? I see you haven't qualified what workbook or worksheet that code operates on, which means it will on whatever is acitve at that moment. Maybe it will help to move your actioning code to a sub in a normal module and use
    Application.OnTime Now, "NewSub"
    To ensure the code is run after all events are processed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() (excel 97)

    What happens if you change your Workbook Open routine to something like this:

    <pre>Private Sub Workbook_Open()
    ThisWorkbook.ActiveSheet.Range("A113").Select
    ThisWorkbook.ActiveSheet.Range("D13").Activate
    Windows(ThisWorkbook.Name).Zoom = True
    ThisWorkbook.ActiveSheet.Range("A1").Select
    End Sub
    </pre>

    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() (excel 97)

    Good News I followed the Beep suggestion but used MsgBox instead (No sound card on this machine) and I now know that the open event is being run

    I tried Jan's suggestion with
    Private Sub Workbook_Open()
    MsgBox "Open Event Working"
    Application.OnTime Now, "NewSub"
    End Sub

    and then had the following in Module1

    Sub NewSub()
    MsgBox "NewSub is working"
    Range("A113").Select
    Range("D13").Activate
    ActiveWindow.Zoom = True
    Range("A1").Select
    End Sub

    But I didn't get the NewSub to run
    I changed the open event from
    Application.OnTime Now, "NewSub"
    to
    Module1.NewSub
    Which ran NewSub and returned the message but it didn't run the rest of the code to resize the worksheet

    I tried Legare's suggestion
    the event ran returning the messagre but nothing else happened
    I copied it straight from what you wrote,
    was I meant to change anything in it to tailor it to my workbook?


    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() (excel 97)

    You should not have needed to change anything to use my code. If you change my code to:

    <pre>Private Sub Workbook_Open()
    Stop
    ThisWorkbook.ActiveSheet.Range("A113").Select
    ThisWorkbook.ActiveSheet.Range("D13").Activate
    Windows(ThisWorkbook.Name).Zoom = True
    ThisWorkbook.ActiveSheet.Range("A1").Select
    End Sub
    </pre>


    Then it should stop right at the beginning of the procedure. What do you see if you step through the code?
    Legare Coleman

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() (excel 97)

    As I step through this code (using f8)

    Private Sub Workbook_Open()
    Stop
    MsgBox "Open Event Working"
    ThisWorkbook.ActiveSheet.Range("A113").Select
    ThisWorkbook.ActiveSheet.Range("D13").Activate
    Windows(ThisWorkbook.Name).Zoom = True
    ThisWorkbook.ActiveSheet.Range("A1").Select
    End Sub

    It opens the VB window
    then switches back to the excel window to display the message
    then back to the VB window where it just highlights each rows in turn but does nothing to the sheet
    when I hold my cursor over ThisWorkbook.Name it shows that the file is the correct file that I want to resize

  8. #8
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() (excel 97)

    I have created an example of the problem
    the file called Launch.xls is used to open the file called Test.xls
    Put them into a directory C:Example or change the code to suit

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

    Re: Workbook_Open() (excel 97)

    The Workbook_Open procedure runs as intended in Excel 2002 (the zoom percentage is set correctly). So we'll have to wait for someone with Excel 97 to test.

  10. #10
    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: Workbook_Open() (excel 97)

    Does the zoom percentage in XL2002 work for "TEST"?

    I can run "launch" and it zooms correctly. In XL97 if I type in the box "test" the file test.xls file opens and runs the Open code, but it does not seem to do anything (it does run thru the code!). I will investigate it some more, but I can confirm (in XL97) that it does not work. In step mode, even entering some of the commands in immediate window do not seem to work.

    If I open Text.xls directly the zoom works without any problem

    Steve

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

    Re: Workbook_Open() (excel 97)

    If I type Test in the Launch spreadsheet and press Enter, the Test.xls workbook is opened and the zoom percentage is set correctly (I tested with various window sizes). So in Excel 2002, opening Test.xls from Launch.xls runs the Workbook_Open event procedure, and it does what it is supposed to do.

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

    Re: Workbook_Open() (excel 97)

    There are some newsgroup threads that indicate that Workbook_Open sometimes fails without an error message in Excel 97. Does the following work for you?
    - Use Insert | Module to insert a standard module in Test.xls.
    - Create a macro Auto_Open (this name is obligatory) with the code from the Workbook_Open event procedure:

    Sub Auto_Open()
    Range("A1:S19").Select
    Range("S19").Activate
    ActiveWindow.Zoom = True
    Range("A1").Select
    End Sub

    - Delete the Workbook_Open event procedure.
    - Change both instances of Workbooks.Open in the Worksheet_Change procedure in Launch.xls to

    Workbooks.Open(FileName:=strFullFilePath).RunAutoM acros xlAutoOpen

  13. #13
    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: Workbook_Open() (excel 97)

    Using the auto_Open works for me in XL97.

    Getting back to the "issue". The issue does not seem to be whether or not the macro runs, it does run. You can add a messagebox in the code to confirm this.
    It does not like to SELECT items when in this "state"

    If/when you step thru the code it goes to the openevent for the macro in test, it goes over the
    Range("A1:S19").Select
    line. It just fails to select anything

    Even from the immediate window it fails to select. I can even select the other workbook and some things will not work. I can (in the immediate window) get and change the VALUEs of cells, I can read the color/colorindex, but I can not set the color or colorindex (no message or error, it just does nothing)

    I haven't checked what else fails, but it seems to be in some "limbo state" where the code runs, but "ignores" some lines of code (but not all of them!).

    It does not seem to be an event issue, it seems to be a failure when opening thru VB. I ran just the openworkbooks line (so I did not have to worry about all the other code):
    Sub test1()
    Workbooks.Open filename:="C:Exampletest.xls"
    End Sub
    or
    Sub test2()
    Workbooks.Open(filename:="C:Exampletest2.xls").Run AutoMacros xlAutoOpen
    End Sub

    Test1 "fails" ("limbo state" as described above), but test2 does not.

    Steve

  14. #14
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() (excel 97)

    thank you very much
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    I've moved the code to Sub Auto_Open in a separate module
    and added .RunAutoMacros xlAutoOpen
    as you suggested

    perfect

    interesting little qwirk in XL97, but glad there is a solution



    Thank you to everyone for your help

Posting Permissions

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