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