Enabling VBA project through the Excel Document

Question

I have finally got all the code working. Thanks to all for some many awesome links. Now one last hurdle.

I am sending this file to multiple stations. Not all the PC's will have Office on them, just might have Excel Viewer. I have made a note in the sheet to explaine they need to run this on a Back Office machine with Office.

So the issue I am having is that when they click on the Email Button or the other Buttons I am getting an error "1004 Programmatic access to the Visual Basics. Project is not trusted."

So I know that I can ask the end users to go through Settings/Macro Section and enable access to VBA Project.

Is this a way to code that into the sheet so when they open the file it just enables the option? Or Can I add yet another Button to Enable it? Below is the VB SCript.

Thanks

Chris

Private Sub EmailWorkbook_Click()

'This subroutine uses ...
'Public Function EmailThisWorkbook(Optional ByVal TO_Recipients As String = "", Optional ByVal CC_Recipients As String = "", Optional ByVal BCC_Recipients As String = "", Optional ByVal Message_SUBJECT As String = "", Optional ByVal Message_BODY As String = "", Optional ByVal FROM_Sender As String = "")

On Error Resume Next

Dim StationCode As String
    StationCode = Left(Environ$("computername"), 3)
    'Grabs the first 3 characters, starting from left side, of the environmental variable 'computername' where the email is being sent from

Dim SendTO As String
    SendTO = "me@somwhere.net"
    'or use... SendTO = "" ... to prompt user for an email address

Dim BSOSubject As String
    BSOSubject = "BSO Scanner Inventory Report for " & StationCode

Dim BSOBody As String
    BSOBody = "BSO Scanner Inventory Report for " & StationCode

Message_Result = ThisWorkbook.EmailThisWorkbook(SendTO, "", "", BSOSubject, BSOBody, "")

If Message_Result = -2 Then
    MsgBox "The Send function is disabled while workbook contains unsaved changes."
End If

If Message_Result = -1 Then
    MsgBox "Email Cancelled"
End If

If Message_Results = 1 Then
    MsgBox "Email Sent Succesfully"
End If

If Message_Result = 0 Then
    MsgBox "Email Failed To Send"
End If

End Sub

Next subroutine:

Private Sub AddDevice_Click()
Dim TargetWorkSheet_Wks As Worksheet
    Set TargetWorkSheet_Wks = ActiveSheet
    TargetWorkSheet_Wks.Unprotect ("chris")

Dim Button_GUI_Obj As OLEObject

Dim InsertionRow_Str As String
    InsertionRow_Str = "C" & TargetWorkSheet_Wks.Range("B25").Value

Dim NewRowNumber_Int
    NewRowNumber_Int = TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(-1).Value + 1

Dim NewButtonID_Str As String

TargetWorkSheet_Wks.Range(InsertionRow_Str).EntireRow.Insert shift:=xlDown
TargetWorkSheet_Wks.Range(InsertionRow_Str).EntireRow.RowHeight = 30

'Column -1
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).HorizontalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).VerticalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).WrapText = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).Interior.Color = RGB(0, 0, 0)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).Borders.Weight = xlThick
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).Font.Name = "Calibri"
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).Font.Size = 18
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).Font.Color = RGB(0, 0, 0)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).Font.Bold = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).Font.Italic = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).Font.Underline = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).Font.Strikethrough = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).Font.Subscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).Font.Superscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1) = ""
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, -1).Locked = True

TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).HorizontalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).VerticalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).WrapText = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).Interior.Color = RGB(150, 54, 52)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).Borders.LineStyle = xlNone
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).Font.Name = "Calibri"
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).Font.Size = 18
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).Font.Color = RGB(255, 255, 255)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).Font.Bold = True
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).Font.Italic = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).Font.Underline = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).Font.Strikethrough = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).Font.Subscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).Font.Superscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0) = NewRowNumber_Int
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 0).Locked = True

TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).HorizontalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).VerticalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).WrapText = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).Interior.Color = RGB(255, 204, 52)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).Borders.Weight = xlThick
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).Font.Name = "Calibri"
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).Font.Size = 18
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).Font.Color = RGB(0, 0, 0)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).Font.Bold = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).Font.Italic = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).Font.Underline = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).Font.Strikethrough = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).Font.Subscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).Font.Superscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1) = TargetWorkSheet_Wks.Range("D18").Value
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 1).Locked = True

TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).HorizontalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).VerticalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).WrapText = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).Interior.Color = RGB(204, 51, 0)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).Borders.Weight = xlThick
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).Font.Name = "Calibri"
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).Font.Size = 18
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).Font.Color = RGB(255, 255, 255)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).Font.Bold = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).Font.Italic = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).Font.Underline = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).Font.Strikethrough = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).Font.Subscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).Font.Superscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2) = TargetWorkSheet_Wks.Range("E18").Value
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 2).Locked = True

TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).HorizontalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).VerticalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).WrapText = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).Interior.Color = RGB(0, 112, 192)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).Borders.Weight = xlThick
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).Font.Name = "Calibri"
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).Font.Size = 18
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).Font.Color = RGB(255, 255, 255)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).Font.Bold = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).Font.Italic = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).Font.Underline = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).Font.Strikethrough = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).Font.Subscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).Font.Superscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3) = TargetWorkSheet_Wks.Range("F18").Value
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 3).Locked = True

TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).HorizontalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).VerticalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).WrapText = True
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).Interior.Color = RGB(230, 230, 230)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).Borders.Weight = xlThick
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).Font.Name = "Calibri"
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).Font.Size = 10
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).Font.Color = RGB(0, 0, 0)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).Font.Bold = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).Font.Italic = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).Font.Underline = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).Font.Strikethrough = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).Font.Subscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).Font.Superscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4) = TargetWorkSheet_Wks.Range("G18").Value
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 4).Locked = True

TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).HorizontalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).VerticalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).WrapText = True
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).Interior.Color = RGB(230, 230, 230)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).Borders.Weight = xlThick
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).Font.Name = "Calibri"
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).Font.Size = 10
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).Font.Color = RGB(0, 0, 0)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).Font.Bold = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).Font.Italic = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).Font.Underline = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).Font.Strikethrough = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).Font.Subscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).Font.Superscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5) = TargetWorkSheet_Wks.Range("H18").Value
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 5).Locked = True

NewButtonID_Str = Year(Now()) & Month(Now()) & Day(Now()) & Hour(Now()) & Minute(Now()) & Second(Now())
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).HorizontalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).VerticalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).WrapText = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Interior.Color = RGB(150, 54, 52)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Borders.LineStyle = xlNone
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Borders(xlEdgeLeft).Weight = xlThick
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Font.Name = "Calibri"
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Font.Size = 18
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Font.Color = RGB(150, 54, 52)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Font.Bold = True
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Font.Italic = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Font.Underline = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Font.Strikethrough = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Font.Subscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Font.Superscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6) = NewButtonID_Str
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 6).Locked = True

Call Add_CommandButton(1, "Save_" & NewButtonID_Str, "Save", InsertionRow_Str, 30, 23.25, 20, 3, "SaveOrUndoDevice")
    Set Button_GUI_Obj = TargetWorkSheet_Wks.OLEObjects("Save_" & NewButtonID_Str)
        Button_GUI_Obj.Visible = False

Call Add_CommandButton(1, "Undo_" & NewButtonID_Str, "Undo", InsertionRow_Str, 33, 23.25, 51, 3, "SaveOrUndoDevice")
    Set Button_GUI_Obj = TargetWorkSheet_Wks.OLEObjects("Undo_" & NewButtonID_Str)
        Button_GUI_Obj.Visible = False
Call Add_CommandButton(1, "Edit_" & NewButtonID_Str, "Edit", InsertionRow_Str, 30, 23.25, 20, 3, "EditDevice")

Call Add_CommandButton(1, "Up_" & NewButtonID_Str, "U", InsertionRow_Str, 18, 23.25, 51, 3, "MoveDevice")
Call Add_CommandButton(1, "Down_" & NewButtonID_Str, "D", InsertionRow_Str, 15, 23.25, 70, 3, "MoveDevice")
    If TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(-1, 0).Value = 0 Then
        Set Button_GUI_Obj = TargetWorkSheet_Wks.OLEObjects("Up_" & NewButtonID_Str)
            Button_GUI_Obj.Enabled = False
    End If
    If TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(1, 0).Value = "x" Then
        Set Button_GUI_Obj = TargetWorkSheet_Wks.OLEObjects("Down_" & NewButtonID_Str)
            Button_GUI_Obj.Enabled = False
    End If
    If NewRowNumber_Int > 1 Then
        PreviousRecord = TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(-1, 6).Value
        Set Button_GUI_Obj = TargetWorkSheet_Wks.OLEObjects("Down_" & PreviousRecord)
            Button_GUI_Obj.Enabled = True
    End If

Call Add_CommandButton(1, "Delete_" & NewButtonID_Str, "X", InsertionRow_Str, 15, 23.25, 85, 3, "RemoveDevice")


TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).HorizontalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).VerticalAlignment = xlCenter
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).WrapText = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).Interior.Color = RGB(0, 0, 0)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).Borders.Weight = xlThick
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).Font.Name = "Calibri"
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).Font.Size = 18
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).Font.Color = RGB(0, 0, 0)
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).Font.Bold = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).Font.Italic = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).Font.Underline = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).Font.Strikethrough = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).Font.Subscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).Font.Superscript = False
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7) = ""
TargetWorkSheet_Wks.Range(InsertionRow_Str).Offset(0, 7).Locked = True

TargetWorkSheet_Wks.Range("B25") = TargetWorkSheet_Wks.Range("B25").Value + 1

TargetWorkSheet_Wks.Protect ("chris")

End Sub

Answer 1

VBA can't turn off VBA security, to help prevent macro viruses. It's a good thing that software can't readily disable virus scanners. However, there may be other solutions. (Technically you can change the registry with VBA, except that changes don't take effect until you restart Excel, and restarting Excel will reset the security-related settings.)

---

Are these users all on the same network? Part of the same workgroup? If so, you could enable macros using the Group Policy Editor like this or similar options here.

---

You could set the folder where your Excel file is located as a Trusted Location.

---

You could send the users a .REG file to update the registry that enables macro trust. This key:

HKEY_CURRENT_USER\Software\Microsoft\Office\_{version}_\Excel\Security\VBAWarnings

can be set to:

1 = Enable all macros (not recommended)
2 = Disable all with notification
3 = Disable all except digitally signed macros
4 = Disable all without notification

More info here or here.

For example, if the user is running Excel 16 on Windows 2000+ you could create a file called EnableMacros.reg like this:

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Security]
"VBAWarnings"=dword:1 

...and distribute it to your users with instructions to execute it before opening Excel. They will get a warning that "Windows could stop working properly", although that can be avoided by opening the .REG file silently, perhaps with a command in a batch file:

regedit.exe /s enablemacros.reg

...although if the user's registry permissions are restricted (and also can't run regedit.exe with elevated privileges), then the change might not take effect.

Disclaimers:

1. Messing with the registry can break things.
2. Defeating security defaults might be a bad idea.