UserForm Microsoft Spreadsheet Control ActiveX warning

[ttt123]

Hi,

I have created a spreadsheet that contains a UserForm and in that userform I have added a Microsoft Spreadsheet Control Object(open the control toolbox, right click and then select Add Additional Controls and select MicroSoft Spreadsheet Control). This object is basically a little spreadsheet that goes in the Form.

There is, however, a problem: for each execution of the program the first time that that user form is utilized, the user is presented with a popup window saying "This application is about to initialize ActiveX controls that might be unsafe. If you trust the source of this file, select OK and the controls will be initialized using your current workspace settings."

I really don't want this to popup in my commercial application. Is there some way to prevent this from happening, for example by making the Spreadsheet a trusted source? (It would be nice if the Macro warning doesn't popup either when the application starts)

ps. I don't want the macro security level to be set to low, but even setting it to low doesn't prevent the activex warning.

Thanks,
Taylour

 

[ttt123]

With respect to messing up Excel users other applications, What I feel would be a good solution is to create two .bat files "HideActiveXWarnings.bat" which sets the registry values to "1 or 4" and "RestoreExcelDefaultActiveXWarnings.bat" which sets both registry values to 2. By running the RestoreExcelDefaultActiveXWarnings.bat, you will be changing everything back to Excel's default and this should not mess up anyone's existing programs since it will make excel do what it does by default. setting the values to 4 will ensure that there is no security risk, but may mess up some excel applications that actually use vba controls, setting to 1 will ensure that all applications run, but there may be security risks. So basically the user is left with the choice: "If the warning's bother them and it won't mess up other applications then set the values to 4" , "If the warnings don't bother them then don't run any .bat file or run the restore batch file"

 

[ttt123]

Problem Solved

Make your application a trusted source by adding a digital signiture so that macros are always run as soon as the workbook is opened. Change the registry keys on the workbookopen event to 1 or 4, change the registry keys back to Excel's default (2) on the workbookbeforeclose event. This will ensure that your application runs smoothly, the user never gets popups warnings, and that it will not affect anyone elses applicaton.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
RestoreActiveXPrompts
End Sub

Private Sub Workbook_Open()
RemoveActiveXPrompts
End Sub

Private Sub RemoveActiveXPrompts()
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

Private Sub RestoreActiveXPrompts()
Dim Import As String

Import = "Windows Registry Editor Version 5.00" & vbCr & vbCr & _
"[HKEY_CURRENT_USER\Software\Microsoft\Office\Common\Security]" & vbCr & _
"""UFIControls""=dword:00000002" & vbCr & _
"[HKEY_CURRENT_USER\Software\Microsoft\VBA\Security]" & vbCr & _
"""LoadControlsInForms""=dword:00000002"

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

-Taylour

 

[Legacy 98055]

Agree with Tusharm though I don't uderstand the logic behind this breaking other controls after having read the KB article at ms and the desription of what these registry settings allow or disallow. The prompt is called a "problem" and ms offers a "workaround" with a clear WARNING.

I suppose a better approach would entail the following. Check with whoever is in charge of policy to see if you will even have the freedom to give the users an option. If so, first of all, provide the user with a plain English warning and a link to the KB article before giving them the option to disable the prompt for this specific control. If the user does not want to be bothered with the prompt, then read in the values that may or may not currently be stored in these dwords, temporarily edit these values, load the userform, and then change the values back to what they were or to the defaults if they did not exist beforehand. The following code only edits the registry temporarily, loads the form initially, and then sets the dwords to the defaults. It should be modified to restore the initial values though they probably only exist on very few machines anyway...

Private InitialActivation As Boolean

Private Sub Workbook_Open()
    ActiveXPrompts True
    InitialActivation = True
End Sub

Private Sub Workbook_Activate()
    Dim uf As New UserForm1
    If InitialActivation Then
        Load uf
        ActiveXPrompts False
        InitialActivation = False
    End If
End Sub

Sub ActiveXPrompts(Optional DisablePrompt = 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:0000000" & IIf(DisablePrompt, 1, 6) & vbCr & _
        "[HKEY_CURRENT_USER\Software\Microsoft\VBA\Security]" & vbCr & _
        """LoadControlsInForms""=dword:0000000" & IIf(DisablePrompt, 1, 2)
        
    Open ThisWorkbook.Path & "\somegibberish134059873459.reg" For Output As #1
    Print #1, Import
    Close #1
    Shell "Regedit.exe /s " & Chr(34) & ThisWorkbook.Path & "\somegibberish134059873459.reg" & Chr(34)
    Kill ThisWorkbook.Path & "\somegibberish134059873459.reg"
End Sub

 

[Reverus]

To Avoid ActiveX Control Warning when initializing a UserForm that contains ActiveX controls:
http://support.microsoft.com/default.aspx?scid=kb;en-us;827742 Link is no good now btw!
1) Click Start, click Run, 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 new subkey.
5) Right-click Security, point to New, and then click DWORD Value.
6) Type UFIControls, and then press ENTER to name the value.
7) Double-click UFIControls.
8) In the Value data box, type 1, and then click OK.
9) Expand the following registry subkey:
HKEY_CURRENT_USER\Software\Microsoft\VBA\
10) Right-click VBA, point to New, and then click Key.
11) Type Security, and then press ENTER to name the new subkey.
12) Right-click Security, point to New, and then click DWORD Value.
13) Type LoadControlsInForms, and then press ENTER to name the value.
14) Double-click LoadControlsInForms
15) In the Value data box, type 1, and then click OK.
16) Quit Registry Editor.

Omg, found this.. I'm using Excel 2016 (Office 365 Student "subscription") I nervously changed my HKEY's in Regedit.. and ka-poof! No more popup from using the "Outlook" popup date (calander) userform! O: It was popping up whenever I opened the workbook. All gone! THanks! Last time I opened Regedit we lost power on the whole western seaboard for 30 seconds! lol ... jk.

 

[kshealy]

To Avoid ActiveX Control Warning when initializing a UserForm that contains ActiveX controls: http://support.microsoft.com/default.aspx?scid=kb;en-us;827742
1) Click Start, click Run, 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 new subkey.
5) Right-click Security, point to New, and then click DWORD Value.
6) Type UFIControls, and then press ENTER to name the value.
7) Double-click UFIControls.
8) In the Value data box, type 1, and then click OK.
9) Expand the following registry subkey:
HKEY_CURRENT_USER\Software\Microsoft\VBA\
10) Right-click VBA, point to New, and then click Key.
11) Type Security, and then press ENTER to name the new subkey.
12) Right-click Security, point to New, and then click DWORD Value.
13) Type LoadControlsInForms, and then press ENTER to name the value.
14) Double-click LoadControlsInForms
15) In the Value data box, type 1, and then click OK.
16) Quit Registry Editor.

To Avoid Macro Security Warning from popping up when VBA Application Starts:
VBA - Create a Digital Signature - Trusted Certificate-VBForums
1) Click Start, click Run,
2) If using OfficeXP, type:
C:\Program Files\Microsoft Office\OFFICE10\SELFCERT.EXE
If using Office2003, type:
C:\Program Files\Microsoft Office\OFFICE11\SELFCERT.EXE

If you do not have the SELFCERT.EXE program you can find it on
your office CD or you can run setup again and install the Office
Tools.
3) Click Ok to run SELFCERT.EXE and then type your name to create a digital signature and then click the OK button
4) Open the Excel document which has Macro warning you want to get rid of
5) Press Alt + F11 to open the Visual Basic Editor
6) click Tools > Digital Signature... and click the Choose button and select the certificate you just created.
7) save and close the Excel application
8) Re-open the application and Check the “Always Trust Macros from this Source” Box that is in the Macro warning screen
9) Click Enable Macros
10) Save and close the application. Whenever you open it from now on, you will not be presented with a Macro warning.

you rock