Trust Access to Visual Basic Project
 

From VBA in Access an XLS workbook and sheets are created with command
buttons and code. If the user does not enable "Trust Access to Visual
Basic Project" the Access VBA traps an error and prompts the user to
enable the setting. (If there is a better way of doing this, please
advise.)

This scheme works, but it requires that all instances of the Excel.exe
be closed -- and the Access program from which the objects were
created. Unless Access is closed, it seems to still hold the value
that the security setting is not enabled. I would like to know how to
clear that.

After the Access program quits the user changes the security setting,
reopens the Access program, everything works just fine.

My goal would be to have the user prompted as is currently occurring,
but also make it so the Access program does not have to be closed and
reopened, and to have code that assures that all instances of the
excel.exe are closed.

This is the current scheme which works but is inadequate. If a user
does not have "Trust Access to Visual Basic Project" enabled an error
occurs he

Dim xlmodule1 As CodeModule
Set xlmodule1 =
objActiveWkb.VBProject.VBComponents(objActiveWkb.W orksheets(strFirstTabName).CodeName).CodeModule

With xlmodule1
StartLine = .CreateEventProc("Click", strButtonName) + 1
.InsertLines StartLine, "On Error Resume Next" & vbCrLf &
_
" Sheets(" & """" & strTabName & """" & ").Select"
& vbCrLf & _
"If Err < 0 Then" & vbCrLf & _
" MsgBox ""This is a cover sheet preview.""" &
vbCrLf & _
" End If" & vbCrLf
End With

The error is trapped he

Err_BuildCoverSheet:

If Err = 1004 Then

MsgBox "Trust Access to Visual Basic Project in Macro Security
Settings Must be Enabled", , "Enable Access to Visual Basic Project"

'''''''''''''''''''
Set xlmodule1 = Nothing
Set objActiveWkb = Nothing
Set objXL = Nothing

Excel.Application.Quit

'forces Access to quit he
Application.Quit acQuitPrompt

'''''''''''''''''''''''''''

Else

MsgBox Err.DESCRIPTION