Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error On Exit (can't find form controls)
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. -- Viv2004 ------------------------------------------------------------------------ Viv2004's Profile: http://www.excelforum.com/member.php...o&userid=26511 View this thread: http://www.excelforum.com/showthread...hreadid=397815 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error On Exit (can't find form controls)
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.Val ue 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 Viv2004 wrote: 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. -- Viv2004 ------------------------------------------------------------------------ Viv2004's Profile: http://www.excelforum.com/member.php...o&userid=26511 View this thread: http://www.excelforum.com/showthread...hreadid=397815 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile error: Can't find project or library | New Users to Excel | |||
help with this error-Compile error: cant find project or library | Excel Discussion (Misc queries) | |||
Compile error: Can't find project or library | Excel Programming | |||
Compile Error, Can'f Find Project or Library | Excel Programming | |||
Compile Error! Can't find project or Library --- Help! | Excel Programming |