Compile Error On Exit (can't find form controls)

[Viv2004]

Hi,

I get a ‘Compile Error: Method or data member not found’ error when I
exit Excel. This seems to be caused by excel trying to access controls
in the worksheet whilst exiting – so these controls are no longer
available. Can anyone tell me how to stop this behaviour or avoid this
error please – perhaps by detecting the fact that excel is shutting
down?

Further details:

I have a worksheet with some form controls, including a listbox and
radio buttons. There is VBA code on the listbox change event that seems
to get executed when exel is shutting down. When this happens, the code
tries to set/read the radio button values using code like the
following:

wksSelection.optnUSD.Value = True

where wksSelection is the code name for a worksheet in the workbook.
optnUSD is the name of the radio button on the
worksheet

It is the references to the form controls that causes the ‘Compile
Error: Method or data member not found’ error.

I would appreciate any ideas you may have. Thank you for your
assistance.

Viv.

 

[SteveB]

Hi Viv2004,

I came across your post when I was searching for a soultion to the same
problem. I am developing an XL 2000 workbook using an activeX Combo box
to provide filtering on some data in a hidden sheet and writing some
data into ActiveX labels on the exposed sheet. The code for the combo's
change event NOW looks like this:

Private Sub cboAccount_Change()
Dim rng As Range, nRow As Integer
Dim lblAddr As Label
Dim lblDirect As Label
Dim lblOutlets As Label
Dim lblBank As Label
Dim lblSortCode As Label
Dim lblAccNo As Label

On Error GoTo Err_Exit

'Update the display panel for the selected Account
Set rng = ThisWorkbook.Names("AccountsData").RefersToRange
nRow = ThisWorkbook.Names("AccsSelRow").RefersToRange.Value
With rng
lblAddr.Caption = .Cells(nRow, 3) & vbLf & .Cells(nRow, 4)
lblDirect.Caption = IIf(.Cells(nRow, 6), "Yes", "No")
lblOutlets.Caption = .Cells(nRow, 5)
lblBank.Caption = .Cells(nRow, 7)
lblSortCode.Caption = .Cells(nRow, 8)
lblAccNo.Caption = .Cells(nRow, 9)

DisplayDeal IsDirect:=.Cells(nRow, 6)

End With
Err_Exit:
End Sub

When I closed Excel with this wbook loaded it generated the Compile
Error on the first ref to the lblAddr control in the With Statement. It
is as if something triggers the compile, probably the closing of the
Wbook triggers the combo change event, but the worksheet and its
controls have gone before the the VBA code tries to run. This leaves
bald refs to controls in the workbook without anything to search
against and the compile fails.

The solution to the problem was to declare the label controls
explicitly in the code even though this was not necessary to compile
the code with everything loaded. There may also be a link to what
libraries are referenced in the project but I am pleased enough to have
killed this one to not explore any further. I hope this isn't too late
to help with your problem but I had to reply as it has bugged me so
much.

SteveB