Workbook_Open Fails (2000 SP3)

Topic

New Reply

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???

Reply | Quote

Replies

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)

Reply | Quote

[indent]

---

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

---

[/indent]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.

Reply | Quote

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..

Reply | Quote

One reason is that if the Thisworkbook module gets corrupted, it is hard to replace (you need to start with a fresh workbook).

Reply | Quote

That makes sense…. Thank you

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote