Compile error on workbook open.

[Roopher]

Hello there!

I have an application (Excel 2007, VBA) that contains some Change_Events on Listboxes on the worksheet. There events work fine when the workbook is open.

However, when I just open the workbook, I get 'Compile Error' messages in these Event-Handler procedures. I guess, it is so because the Change-Event triggers when the 'targets', i.e. the Listbox, are not fully loaded/populated/not readily built.

Is there a way to delay or disable Excel's macro compilation when opening the workbook until all listbox elements are properly populated? Or, can I disable/enable the event procedures?

I also have an 'Workbook Open' event with 'Application.EnableEvents = False/True', but no relief from this as the above mentioned change events seem to fire earlier.

Thank's alot,

 

[ScottD]

Not sure if it'll help, but maybe a time delay on the workbook open code

Application.Wait Time + TimeSerial(0, 0, 5)

That will give it a 5 second time delay before it runs the rest of the code

 

[GTO]

You may wish to post the code to Workbook_Open() as well as any procedures call during the Open event.

Mark

 

[Roopher]

Not sure if it'll help, but maybe a time delay on the workbook open code

Application.Wait Time + TimeSerial(0, 0, 5)

That will give it a 5 second time delay before it runs the rest of the code

Thanks for the idea, but I think the Change-Event triggers before the Workbook Open Event. In fact, it seem to trigger when their objects (the listboxes) are being built.

 

[Roopher]

You may wish to post the code to Workbook_Open() as well as any procedures call during the Open event.

Mark

Thanks for the post. The code is here:

---
Private Sub Workbook_Open()

Application.EnableEvents = False

Call ControlBoxCode.BoxInit

Application.EnableEvents = True

End Sub
---



And here one of the Change Event handlers:

---
Private Sub cboFundsSelect_Change()

Me.lbxStrategySelect.ListFillRange = "grng_5_FS" ' fill with fund's strategy list
Me.lbxStrategySelect.Selected(0) = True ' tick the first strategy

End Sub
---

The error points to the '.lbxStrategySelect' - part in the first line.

 

[RoryA]

What does ControlBoxCode.BoxInit do?

 

[Roopher]

Hello rorya!

The controlbox code initializes and fills 2 listboxes (ActiveX) that are on sheet, i.e. setting their properties, assigning named ranges to fill the list boxes. Here is one example:

---
Private Sub...
' initialize the strategies listbox
With wksFundsSheet.lbxStrategySelect
.ListFillRange = ThisWorkbook.Names(sStrat)
.ListIndex = 0
.ColumnCount = 2
.TextColumn = 2
.BoundColumn = 1
.ColumnWidths = "0;"
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti
.Height = 85
.Locked = True
.IntegralHeight = False
.Selected(0) = True
End With
End Sub
---



Apparently, before these initializations are completed, the change events on these object fire, and find listboxes that are not fully inititialized. E.g. the named ranges used to fill the listbox is not fully loaded by Excel?

My solution would be to have all events temporarily disabled, or the Event procedures disabled somehow for some seconds. Do you have an idea on this?

Best regards,

 

[RoryA]

Enableevents doesn't work for controls. As a general rule I don't think (even though I do it sometimes) it's a great idea to run code directly from the Open event. You are usually better off using Application.Ontime with a delay of say 1s to give Excel time to finish all its startup business.
I also try to avoid actually 'binding' controls to worksheets - I'd rather use code to load/retrieve their data as and when I want. (of course, I try and avoid ActiveX controls on worksheets altogether!)

 

[Roopher]

Thanks for your valued input.

Best regards,