Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workbook_Open() and Templates (XL97 sr2 on Win 2K)

    <P ID="edit" class=small>(Edited by stuck on 04-Dec-03 15:49. The original text doesn't seem to be the whole story!)</P>Following on from <post#=318713>post 318713</post#> where I learnt that UDFs can screw up the Workbook_Open event I now need advice on using this event in a template.

    I have a template with some code in This Workbook , Workbook_Open and have discovered that:
    1) When I open the actual template the code runs
    2) If I create a sheet based upon the template the code doesn't run
    3) If, having saved the sheet based upon the template, I reopen the sheet the code runs

    I guess nothing happens in 2) because the sheet holding the code isn't actually being opened, it's being created/copied/cloned somehow so the event doesn't happen but since the new sheet created inherits the code from the template then reopening the saved sheet does make the event fire.

    Is there a way round this, i.e. can Workbook_Open code in a template be activated as a sheet is created from the template?

    stuck
    edited.....
    The failure of the code to run as described above seems to be limited to the case where you double click the template from Explorer. If you create a sheet based upon the template using XL's File New dialog the Workbook_Open code runs.

  2. #2
    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() and Templates (XL97 sr2 on Win 2K)

    Have you tried adding the code that the workbook open calls to also the worksheet activate event? this should then keep it running in 1 and 3 but add "2" to it. 2 does not "open" the workbook, so it is not surprising that the workbook open event is not triggered.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() and Templates (XL97 sr2 on Win

    I'll look into that idea but that may create other problems. For example, if there are multiple sheets open, the user switches to another sheet and then returns the focus to the sheet with the code in the _Activate section, won't the code run again?

    stuck

  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: Workbook_Open() and Templates (XL97 sr2 on Win

    Yes, you are correct, bad call on my end.

    I tried playing with this (als xl97 sr2, on win 2000), and I could not create a sheet and NOT run the code. How did you do it?

    1)If i open the template (xlt) file, the workbook open triggered and ran called a test macro.
    2) if if click file-new and created a new workbook, the code runs (from the new workbook)
    3) if I dbl-click the template file, the code runs (from the new workbook). [I did not get a failure like you report]

    How did you create a sheet from the template and not have it run? Perhaps I don't understand completely what you are doing.

    Steve

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

    Re: Workbook_Open() and Templates (XL97 sr2 on Win

    Try putting your code in the Workbook_NewSheet event.

    <pre>Private Sub Workbook_NewSheet(ByVal Sh As Object)
    </pre>

    Legare Coleman

  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() and Templates (XL97 sr2 on Win

    He is probably using Insert/WorkSheet.
    Legare Coleman

  7. #7
    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() and Templates (XL97 sr2 on Win

    I was thinking of this, but the thisworkbook object code and the modules will not be transfered by inserting a sheet. He mentioned that he could insert the sheet, then save and close and the workbook_open macro would run. There should be no workbook_open to run after just inserting a sheet.

    Steve

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

    Re: Workbook_Open() and Templates (XL97 sr2 on Win

    If you create a new workbook based on the template, then Insert/Worksheet, I think you get the behavior he described.
    Legare Coleman

  9. #9
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() and Templates (XL97 sr2 on Win

    Sorry but I'm not doing Insert|Worksheet.

    I double clicked the .xlt file in Explorer, which opened a file based on the .xlt rather than opening the xlt itself. When I did that the Workbook_Open code did not run. If I right clicked in Explorer and chose 'Open' then as the actual .xlt opened the code ran, i.e. Steve has understood correctly but he couldn't reproduce the failure. In which case maybe my problems are again related to my UDFs.

    stuck

  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() and Templates (XL97 sr2 on Win

    This doesn't answer how you did #2 in the original <post#=318811>post 318811</post#>. How did you "create a sheet based upon the template" that did not trigger the open event?

    You might want to test it by making a very simple template with a workbook open event and do your tests to rule out some of the other code issues.

    Steve

  11. #11
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() and Templates (XL97 sr2 on Win

    In my original post, in 2), where I said "create a sheet based upon the template" I meant what I said in my edit to my original post and try to clarify in <post#=319088>post 319088</post#> . I used Explorer and double clicked on the .xlt filename. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

    The folder the file was sitting in was not a designated template folder, i.e. nowhere in any Office application was that folder named as a template folder, either user or workgroup. Nor was it a startup or alternate startup folder.

    I think we are getting lost in the words here, I never meant it to get so complicated, sorry!

    stuck

  12. #12
    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() and Templates (XL97 sr2 on Win

    The issue is whether you were creating a new worksheet or creating a new workbook. When you create a workbook, the workbook open event should trigger. When you create a new worksheet from a template, there is no reason why the workbook event should trigger, but as Legare pointed out, the NewSheet event should be triggered.

    Whenever I dblclick a template, it is the same as doing a File new and choosing that template. A new workbook is created from that template and the sheet code, the thisworkbook code and the modules are all there (and the open event is triggered). I made only a simple "test" workbook, so if yu have issues with UDFs mine would not see it.

    One way to test is just add 2 lines to each of the UDFs:

    "udfname" [replace with the name] = "TEST"
    Exit function

    If the templates work as expected, then it confirms the UDF issue.
    Then remove the lines 1 by one, until you find the UDF that gives you problems and work with fixing the problems in the coding of the udf.(you just might need some error trapping in the function.
    Steve

  13. #13
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() and Templates (XL97 sr2 on Win

    Steve,

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> I see my mistake now, sloppy use of the terms worksheet and workbook. As a scientist I should have known better the importance of using the correct word to describe an issue.

    stuck

Posting Permissions

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