Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Carson City, Nevada, USA
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Workbook_Open Fails (2000 SP3)

    Hello, I am having a problem with the Workbook_Open event in the ThisWorkbook sheet. I am trying to create a custom toolbar with three command buttons on it. I was able to get this done in a standard module (module1 in the attached) and it works well. I really want this to happen when the Workbook opens so I copied and pasted the code to the ThisWorkbook sheet in the Workbook_Open event. When I open the workbook, I get an "object variable or With Block variable not set" error. Everything seems be identical to the code in Module1 so now I am confused as to why it doesn't work.

    Perhaps it has something to do with the fact the the ThisWorkbook shhet is a Class Module???

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

    Re: Workbook_Open Fails (2000 SP3)

    I would recommend calling the AddToolbars procedure in Module1 from the Workbook_Open event procedure. Code in a standard module is easier to debug than code in a class module such as ThisWorkbook.

    Sub Workbook_Open()
    Call AddToolbars
    End Sub

    (BTW you could have made the code run in ThisWorkbook by replacing all instances of CommandBars with Application.CommandBars)

  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 Fails (2000 SP3)

    <hr>Perhaps it has something to do with the fact the the ThisWorkbook shhet is a Class Module???<hr>
    Exactly so.

    Any object that you do not qualify, but does have a parent object normally (like the Commandbars collection) is assumed to be a member of the class the unqualified object is used in. So when the Thisworkbook module contains the code Commandbars("whatever"), VBA tries to do ThisWorkbook.Commandbars("Whatever"). But Commandbars are no member of the Thisworkbook object, hence the Object variable or withvariable not set error.

    When this is done inside a normal module, VBA assumes "Application" to be the default parent object, which is why your code did work overthere.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Lounger
    Join Date
    Nov 2001
    Location
    Carson City, Nevada, USA
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open Fails (2000 SP3)

    Thanks to both of you for the recommendation (Hans) and description (Jan) of why it didn't work. I'm a little embarrassed in that I didn't spot the problem myself as the code just above the failure point worked and it had the Application qualifier. Maybe it was just the late hour....

    Hans, I failed to say that your suggestion of calling the code in the normal module was workaround. I am curious though, why do you "recommend" this method instead of having the reside directly in ThisWorkbook? Perhaps to keep all of the related code in one module?

    Thank you both again..

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

    Re: Workbook_Open Fails (2000 SP3)

    One reason is that if the Thisworkbook module gets corrupted, it is hard to replace (you need to start with a fresh workbook).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Lounger
    Join Date
    Nov 2001
    Location
    Carson City, Nevada, USA
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open Fails (2000 SP3)

    That makes sense.... Thank you

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

    Re: Workbook_Open Fails (2000 SP3)

    One reason for keeping the body of the code in a standard module, and put as little code as possible in worksheet modules and ThisWorkbook is that code in a standard module is easier to debug than code in a class module.
    Another reason is that all modules deteriorate over time. As they are edited, invisible crud builds up. With a standard module, you can get rid of this crud by exporting it to a plain text file, deleting the module, then importing the text file. Rob Bovey's VBA Code Cleaner automates this process. Since worksheet modules and ThisWorkbook cannot be deleted, they keep on accumulating crud. The less you edit them, the better it is.

  8. #8
    Lounger
    Join Date
    Nov 2001
    Location
    Carson City, Nevada, USA
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open Fails (2000 SP3)

    Thanks Hans, that is what I will do. I also downloaded the code cleaner and will use it regularly.

    Thanks again for ALL of 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
  •