I am trying to remove UserForms activex prompts when opening Excel and I am having problems to access Shell command. Consider the code below. I have made tests with two computers (A and B).
Computer A: from year 2014, had Windows 8, 64bit Home single language installed and then uptaded to Windows 10, 64bit Home single language with latest updates; Office Professional 2016 Plus, Microsoft Excel 2016 32bits, Version 1801 (built 9001.2138)
Computer B: from year 2017, had Windows 10, 64bit home single language installed with latest updates; Office Professional 2016 Plus, Microsoft Excel 2016 32bits, Version 1801 (built 9001.2138)
Private Sub RemoveActiveXPrompts()
Application.ScreenUpdating = False
Dim Import As String
Import = "Windows Registry Editor Version 5.00" & vbCr & vbCr & _
"[HKEY_CURRENT_USER\Software\Microsoft\Office\Common\Security]" & vbCr & _
"""UFIControls""=dword:00000004" & vbCr & _
"[HKEY_CURRENT_USER\Software\Microsoft\VBA\Security]" & vbCr & _
"""LoadControlsInForms""=dword:00000004"
Dim FileNum As Integer
FileNum = FreeFile
Open ThisWorkbook.Path & "\tempRegFile.reg" For Output As #FileNum
Print #FileNum, Import
Close #FileNum
Shell "Regedit.exe /s " & Chr(34) & ThisWorkbook.Path & "\tempRegFile.reg" & Chr(34)
Kill ThisWorkbook.Path & "\tempRegFile.reg"
End Sub
[Problem 01:] The above works in Computer A with no VBA errors, but if you open the regedit.exe in order to check what had been written, you notice that no modifications were made. The DWord value of "UFIControls" is still 00000002 from factory, so does the value of "LoadControlsInForms" equals to 00000002.
[Problem 02:]
The above generates a VBA error in Computer B "Time execution error n.5. Invalid procedure call or argument". I had then altered the next-to-last line of code: Shell "Regedit.exe /s " & Chr(34) & ThisWorkbook.Path & "\tempRegFile.reg" & Chr(34) to: Shell (Environ("Windir") & "\Sysnative\reg.exe " & Chr(34) & ThisWorkbook.Path & "\tempRegFile.reg" & Chr(34)). It did not generate VBA errors this time, but as above, no changes were made in the registry.
Question:1 How to make this code effective by altering the registry in both computers A and B without issuing errors?
Question:2 Why doesn´t the original code work in computer B?
---------------------------[CORRECTION MADE: now working]------------------------
Private Sub RemoveActiveXPrompts() '<------- Call RemoveActiveXPrompts in Private Sub Workbook_Open()
Application.ScreenUpdating = False
Shell "reg add ""HKEY_CURRENT_USER\Software\Microsoft\Office\Common\Security"" /f /v ""UFIControls"" /t REG_DWORD /d 3 /reg:32"
Shell "reg add ""HKEY_CURRENT_USER\Software\Microsoft\VBA\Security"" /f /v ""LoadControlsInForms"" /t REG_DWORD /d 3 /reg:32"
End Sub
It never hurts stating that both "UFIControls" and "LoadControlsInForms" entries were active. In case your specific entry simply does not exist, you should manually create it BEFORE running this code snippet, as in the step-by-step example below. Otherwise, no errors will appear and nothing will be written either.
1) Hold "WINDOWS" and "X" keys at the same time, select execute, type regedit, and then click OK.
2) Expand the following registry subkey: HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\Common
3) Right-click Common, point to New, and then click Key.
4) Type "Security", and then press ENTER to name the newly created subkey.
5) Right-click Security, point to New, and then click DWORD Value.
6) Type "UFIControls", and then press ENTER to name the newly created value.
One should bear in mind that once the Excel is closed, these DWord values will be there forever. One MUST change the registry keys back to Excel's default, otherwise, anyone who uses this machine may have some installed products ruined or future products that they acquire may not work at all. To reverse that, another Sub is created and placed as below. The procedure is the same. Just change the DWord: "4" to the original value which is DWord: "2"
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
RestoreActiveXPrompts
End Sub
