Can't delete forms using code
 

Hi folks.
Banging my head against a brick wall here. I've devised an Excel
template where users complete cells by choosing a value from listboxes
which appear when they click a corresponding macro button. A few of
these listboxes are populated by values from an Access database. See
code block below (but most are filled using the normal Initialize
event of the form.

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Decent Homes Data Systems\" & _
"AddressLookup.mdb"

sSQL = "SELECT OfficerName FROM qryOfficers"

rst.Open sSQL, sConn
' Copy recordset to the range
rg.CopyFromRecordset rst

' Adjust column sizes
rg.CurrentRegion.Columns.AutoFit
' Close the recordset

rst.Close
' Clean up.
Set rst = Nothing
Set rg = Nothing
Exit Sub

Each time users complete and save the worksheet it saves a copy of the
data as a unique .xls file. These are pretty hefty files - about 700K
because they still contain all of the user forms.

I force users to use a "Close" (cmdClose) button to exit the worksheet
on close, because this contains validations as well as code to delete
macro buttons from the front end, turn toolbars back on and delete
unwanted hidden sheets etc etc. It also saves the worksheet in a
unique name.

This works well - but the code to delete the 37 user forms doesn't
work when called as part of the Close Event (cmdClose) I've created.

When I put the delete code block into a Public Sub associated with a
macro button, all forms are deleted immediately. However, calling this
Public Sub from the cmdClose procedure (not the Workbook Close Event)
leaves the forms well and truly alive and kicking. All other calls to
sub procedures are performed without a hitch.

I've tried altering the order of the called procedure but this makes
no difference. Tried pasting the code directly into the cmdClose
procedure but this makes no difference. Only way to delete the forms
is to click the macro button holding the code. This latter option is
no good because I need to ensure form is fully completed etc etc and
want to control how users close (and automatically save) the workbook.

Has anybody got any ideas please? Code below is what I'm using for the
delete. Would appreciate any help since the resultant files are bigger
than Dolly Parton's front end.
Thanks Greg.


Dim VBComp As Object '(late binding)
Set VBComp = ActiveWorkbook.VBProject.VBComponents

With ActiveWorkbook.VBProject
For Each VBComp In .VBComponents
If VBComp.Type = 3 Then
.VBComponents.Remove VBComp
End If
Next VBComp
End With