PDA

View Full Version : Solved: Modify Personal.xls Not Trusted Error



SherryO
12-19-2006, 09:13 AM
Hi,
I'm using the following code to modify personal.xls on open from another file to install a toolbar and menu items. When I run it on other's machines, I get an error that the source is not trusted. Can anyone help me get beyond this message?
Thank you so much.

Sub ModToPersonal()
Application.ScreenUpdating = False
Dim Filt As String, Title As String, FilterIndex As Integer, i As Integer, FileName
Dim blnPersonal As Boolean
Dim FSO As Object, Folder As Object, File As Object
Dim PersonalXLS As Workbook

'****Create an instance of the FileSystemObject and obtain the excel startup folder
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder(Application.StartupPath)

'****See if Personal.xls already exists
For Each File In Folder.Files
If UCase(File.Name) = "PERSONAL.XLS" Then
If WorkbookIsOpen(File.Name) Then
Set PersonalXLS = Application.Workbooks(File.Name)
Else
Set PersonalXLS = Application.Workbooks.Open(File.Path)
End If

blnPersonal = True
Exit For
End If
Next

'****If Personal.xls was not found, create Personal.xls workbook and hide it
If blnPersonal = False Then
Set PersonalXLS = Application.Workbooks.Add
PersonalXLS.SaveAs (Application.StartupPath & "\PERSONAL.xls")
Windows("PERSONAL.xls").Visible = False
End If

PersonalXLS.VBProject.VBComponents.Import ("c:\Project Management\Admin\Code\SensisPM.bas")
ExitHere:
'****Clean up
PersonalXLS.Save
Set PersonalXLS = Nothing
Set FSO = Nothing
Set Folder = Nothing
Set File = Nothing
Application.ScreenUpdating = True
End Sub

'Function thanks to John Walkenbach
Private Function WorkbookIsOpen(wbName) As Boolean
'Returns TRUE if the workbook is open
Dim wb As Workbook
On Error Resume Next
Set wb = Application.Workbooks(wbName)
If Err = 0 Then WorkbookIsOpen = True Else WorkbookIsOpen = False
End Function

CBrine
12-19-2006, 09:17 AM
SherryO,
Have you checked the programmatic access flag in the macro security setup for those machines? It's most likely not checked, and causing this problem.

1. Select tools...Macro.....Security from the menu.
2. Click on the trust publisher's tab of the dialog that appears.
3. Make sure the Trust access to Visual Basic Project is selected.

HTH
Cal

SherryO
12-19-2006, 09:42 AM
Thank you, that was it!! Is there anyway to set that flag using VBA? I'm assuming no, since that would defeat the purpose of it, but...

Zack Barresse
12-19-2006, 09:49 AM
There is, but you'll never get it from this board. We consider that hacking. (No offense.) :)

How is New York this time of year? My brother had snow in Syracuse. :)