Excel consistently crashes during macro

[iroosma]

I consistenly get the following error message when I open my Excel file
Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the invonvenience.

Error signature
AppName: excel.exe AppVer: 11.0.8307.0 AppStamp:49ee85df
ModName: unknown ModVer: 0.0.0.0 ModStamp:00000000
fDebug: 0 Offset: 00000000

My Excel file automatically starts a macro that opens a userform when the file is opened. The userform_initialize function calls a sub that containes the following code.

When I open the Excel file I do not get the error message if the last time I opened the file I selected disable macros. I consistently get the error message when I open the Excel file and the last time I opened the file I enabled macros and saved the file afterwards.

I get the error in the following code between msgbox 4 and msgbox 5. If I move the last two lines under msgbox 1 then I get the error message between msgbox 1 and msgbox 2.

I am not sure what is causing this error message but it seems to be related to the combobox code. I would normally be able to debug my code but I cannot when excel crashes.

Any and all help will be so appreciated.

Thanks,

MsgBox ("1")
Sheets(otherLookup).Select
MsgBox ("2")
UserForm1.ComboBoxa.RowSource = Sheets(otherLookup).Range(char(lineofBusCol) & "2", Range(char(lineofBusCol) & "65536").End(xlUp)).Address
MsgBox ("3")
UserForm1.ComboBox4.RowSource = Sheets(otherLookup).Range(char(lineofBusCol) & "2", Range(char(lineofBusCol) & "65536").End(xlUp)).Address
MsgBox ("4")
UserForm1.ComboBox6.RowSource = Sheets(otherLookup).Range(char(lineofBusCol) & "2", Range(char(lineofBusCol) & "65536").End(xlUp)).Address
MsgBox ("5")
UserForm1.ComboBox5.Clear
MsgBox ("6")
UserForm1.ComboBox5.AddItem ("")
MsgBox ("7")
UserForm1.ComboBox5.AddItem ("Yes")
UserForm1.ComboBox5.AddItem ("No")

 

[RoryA]

Hard to say from that without a file but my observations would be:
1. You should use a variable to refer to the form, not the default instance (i.e. not Userform1)
2. I avoid binding controls to worksheets because it causes all sorts of problems. Load the range into the List property instead.
3. If you are calling the code directly from the Open event, try using OnTime to schedule the code instead. That should give Excel time to finish all its startup processes properly before it runs your code.

 

[iroosma]

Number 1 fixed my issue.

I changed userform1 to a variable and everything now works.

Thanks so much,