VBA errors when opening xl document

Question

I added some VBA code for my xl sheets . This code seems to compile when i open the WorkBook. An exemple of the type of error i got when i open the document .

Private Sub ComboBox1_Change()
If ComboBox1.Value = "GGS" Then
Sheets("Index").CommandButton2.Visible = True
==> Sheets("Index").Label6.Visible = True
Sheets("MNO").CommandButton5.Visible = True
Sheets("ServiceProvider").CommandButton5.Visible = True
Sheets("ServiceDeployer").CommandButton5.Visible = True
Sheets("CardVendor").CommandButton5.Visible = True
Sheets("LoadFile").CommandButton5.Visible = True

Else

Sheets("Index").Label6.Visible = False
Sheets("Index").CommandButton2.Visible = False
Sheets("MNO").CommandButton5.Visible = False
Sheets("ServiceProvider").CommandButton5.Visible = False
Sheets("ServiceDeployer").CommandButton5.Visible = False
Sheets("CardVendor").CommandButton5.Visible = False
Sheets("LoadFile").CommandButton5.Visible = False
End If

I got a "Run-time errror '438' : Object doesn't support the property or method " on the marked line Sometimes there is the same error for this line in another sheet

Me.ListBox1.Clear

So I end the debuging , then all the code works properly ,even those last lines .

Is it possible to disable the auto compilation at the opening of the document ?

It seems that the VBA editor tries to run the code before the View is created. The debug just drop an "Object required" .I checked before stoping debuging and the object was not in the objects list .When i stoped the debuging then checked the list, the object was there

(I'm French so there might have some english mistakes )

Thank you for reading me

Spikeze

EDIT : I found the problem.

The "ComboBox1.Style " is on fmStyleDropDownList to avoid the user to edit the options . But this option makes the Combobox automatically choose the first option when the document opens. So I guess it runs "ComboBox1_Change()" when the first option is choosed but some view elements are not loaded at this time and the VBA editor drops an "Objec required".

I set the style to fmStyleDropDownCombo and fmStyleDropDownList when the sheets is active but if the WorkBook is saved and reopened the style is on fmStyleDropDownList again and I got the error again .

To solve it I had those sub

Private Sub Worksheet_Activate()
ComboBox1.Style = fmStyleDropDownList
End Sub

In the Index sheet code and

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Index").ComboBox1.Style = fmStyleDropDownCombo
End Sub

In the WorkBook code .

Spikeze

brainac brainac · Accepted Answer · 2014-04-16T10:16:27

You can just use error handling. Not the best solution, but it should work. If you are sure these objects exist, when you need them, you can use

On Error Resume Next

Or handle the error properly, with checking code error and resuming only on 438

VBA errors when opening xl document

1 Answers