Trust Access to Visual Basic Project

T

tcb

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 here:

Dim xlmodule1 As CodeModule
Set xlmodule1 =
objActiveWkb.VBProject.VBComponents(objActiveWkb.Worksheets(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 here:

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 here:
Application.Quit acQuitPrompt

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

Else

MsgBox Err.DESCRIPTION
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top