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

 

[tusharm]

From what I recall -- and I definitely don't recall the specifics -- there are certain types of warnings associated with running external processes that simply cannot be avoided. They are a security measure to try and thwart malicious code.

 

[ttt123]

I did some reasearch and found out a little about getting a digital signiture (from VeriSign) on your Excel application and this prevents the the macro warning. Does anyone know if this will prevent the ActiveX warning as well?

 

[ttt123]

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:
http://www.vbforums.com/showthread.php?t=285212
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.

 

[Norie]

Taylour

The first solution involves altering the registery - are you sure the users of this 'commercial application' will allow that?

 

[ttt123]

It seems that certain ActiveX controls in UserForms, such as the Microsoft Spreadsheet Control Object can be loaded properly in safe mode. This means that you can actually set both registery values to 4 and still load the form with the Spreadsheet control object in it. Setting the registry value to 4 is the safest it gets (whereas 1 is the most unsafe). Setting the values to 4 is equivalent to clicking cancel in the popup window that is presented when loading the form since clicking cancel will load it in safe mode.

I think if we require changing the registry value to 4 it should be ok since it is basically giving the end user more security for their Excel applications than what the default security level is.

I am intersested in knowing, it is possible to create a batch file that will automatically create these registry keys and if so, how would it be done?

-Taylour

 

[Legacy 98055]

You may call indirectly from the commandline using shell in VBA...
There may be a direct way as well but I do not know how.

Download the zip file and extract.

For testing...
Import "ShowWarningPrompt.reg" into the registry. This will add or edit the keys/dwords mentioned by taylourmackay. This will set the value of the two dwords to 0 and will display the activeX warning prompt hereafter.

Open the workbook, "RemoveActiveX_SecurityPrompts.xls". If the procedure runs correctly from Workbook_Open, you should get a prompt the first time you open the workbook, but no more thereafter.

The code creates a reg file on the fly, opens regedit via shell, imports the reg file using the 's' switch (silent), and then deletes the temp reg file.

Ideally, you would perform this activity as part of a setup solution. However, this should do well enough in removing the prompt in all but the initial opening.

RemoveActiveXWarningPrompt.zip

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');" ><TD><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_Open()
       RemoveActiveXPrompts
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
  
  <font color="#0000A0">Sub</font> RemoveActiveXPrompts()
       <font color="#0000A0">Dim</font> Import <font color="#0000A0">As</font> <font color="#0000A0">String</font>
      
       Import = "Windows Registry Editor Version 5.00" & vbCr & vbCr & _
           "[HKEY_CURRENT_USER\Software\Microsoft\Office\Common\Security]" & vbCr & _
           """UFIControls""=dword:00000001" & vbCr & _
           "[HKEY_CURRENT_USER\Software\Microsoft\VBA\Security]" & vbCr & _
           """LoadControlsInForms""=dword:00000001"
          
       <font color="#0000A0">Open</font> ThisWorkbook.Path & "\somegibberish134059873459.reg" <font color="#0000A0">For</font> <font color="#0000A0">Output</font> <font color="#0000A0">As</font> #1
       <font color="#0000A0">Print</font> #1, Import
       <font color="#0000A0">Close</font> #1
       Shell "Regedit.exe /s " & Chr(34) & ThisWorkbook.Path & "\somegibberish134059873459.reg" & Chr(34)
       Kill ThisWorkbook.Path & "\somegibberish134059873459.reg"
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table>
<button onclick='document.all("TomsCode2").value=document.all("TomsCode2").value.replace(/<br \/>\s\s/g,"");document.all("TomsCode2").value=document.all("TomsCode2").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("TomsCode2").value);'>Copy to Clipboard</BUTTON> RemoveActiveXWarningPrompt.zip<textarea style="position:absolute;visibility:hidden" name="TomsCode2" wrap="virtual">
Private Sub Workbook_Open()
RemoveActiveXPrompts
End Sub

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:00000001" & vbCr & _
"[HKEY_CURRENT_USER\Software\Microsoft\VBA\Security]" & vbCr & _
"""LoadControlsInForms""=dword:00000001"

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

 

[ttt123]

Thanks Tom,

That worked great.

-Taylour

 

[tusharm]

That's interesting research and nice to know. But, this raises a serious performance and ethical issue.

Do keep in mind that such changes will affect the behavior of *all* Office programs for *all* ActiveX controls including controls and add-ins already installed. Obviously, you know your customers better than I do, but you will potentially impact how they interact with other products. Some installed products may stop working and future products that they acquire may not work. Essentially, you *don't know* if they have ActiveX controls that they need to run in the current manner!

Hopefully, you will give your customers a choice in this matter. If you don't, it is hard for me to see how your behavior is any different from someone installing malicious code. Why? Because, by changing how some programs on someone else's computer treat ActiveX controls you have potentially damaged software they rely upon.

And, if you gave me a choice, I would definitely not let you make the registry changes. I would live with the occassional warning rather than jeapordize other software I am already use or might want to use in the future.

It seems that certain ActiveX controls in UserForms, such as the Microsoft Spreadsheet Control Object can be loaded properly in safe mode. This means that you can actually set both registery values to 4 and still load the form with the Spreadsheet control object in it. Setting the registry value to 4 is the safest it gets (whereas 1 is the most unsafe). Setting the values to 4 is equivalent to clicking cancel in the popup window that is presented when loading the form since clicking cancel will load it in safe mode.

I think if we require changing the registry value to 4 it should be ok since it is basically giving the end user more security for their Excel applications than what the default security level is.

I am intersested in knowing, it is possible to create a batch file that will automatically create these registry keys and if so, how would it be done?

-Taylour

[/b]

 

[ttt123]

Just incase anyone wants to know, this is what the code looks like when ported over to a batch file (ex setup.bat). Runing this setup.bat file is equivalent to Right_Click's Workbook_Open event:

@ECHO OFF
:: No parameters required
IF NOT [%1]==[] GOTO Syntax

:: Choose the correct command processor for the current operating system
SET _cmd=
:: Variable to add shortcut to menu entry (NT only,
:: since COMMAND.COM cannot echo an ampersand)
SET _=
ECHO.%COMSPEC% ¦ FIND /I "command.com" >NUL
IF NOT ERRORLEVEL 1 SET _cmd=command.com /e:4096
ECHO.%COMSPEC% ¦ FIND /I "cmd.exe" >NUL
IF NOT ERRORLEVEL 1 SET _cmd=cmd.exe
IF [%_cmd%]==[cmd.exe] SET _=^&

:: Create a temporary .REG file
> "%Temp%.\hideActiveXwarning.reg" ECHO Windows Registry Editor Version 5.00
>>"%Temp%.\hideActiveXwarning.reg" ECHO.
>>"%Temp%.\hideActiveXwarning.reg" ECHO [HKEY_CURRENT_USER\Software\Microsoft\Office\Common\Security]
>>"%Temp%.\hideActiveXwarning.reg" ECHO "UFIControls"=dword:00000004
>>"%Temp%.\hideActiveXwarning.reg" ECHO [HKEY_CURRENT_USER\Software\Microsoft\VBA\Security]
>>"%Temp%.\hideActiveXwarning.reg" ECHO "LoadControlsInForms"=dword:00000004

:: If neither COMMAND.COM nor CMD.EXE then skip this step
IF [%_cmd%]==[] ECHO Skipping "Command Prompt Here" entry
IF [%_cmd%]==[] GOTO Merge

ECHO Adding "Command Prompt Here" entry
:: Add Command Prompt Here for files
>>"%Temp%.\hideActiveXwarning.reg" ECHO [HKEY_CLASSES_ROOT\*\shell\prompt]
>>"%Temp%.\hideActiveXwarning.reg" ECHO @="Command Prompt Here"
>>"%Temp%.\hideActiveXwarning.reg" ECHO.
>>"%Temp%.\hideActiveXwarning.reg" ECHO [HKEY_CLASSES_ROOT\*\shell\prompt\command]
>>"%Temp%.\hideActiveXwarning.reg" ECHO @="%_cmd% /k cd \"%%1\\..\""
>>"%Temp%.\hideActiveXwarning.reg" ECHO.
:: Add Command Prompt Here for directories
>>"%Temp%.\hideActiveXwarning.reg" ECHO [HKEY_CLASSES_ROOT\Directory\shell\prompt]
>>"%Temp%.\hideActiveXwarning.reg" ECHO @="Command Prompt Here"
>>"%Temp%.\hideActiveXwarning.reg" ECHO.
>>"%Temp%.\hideActiveXwarning.reg" ECHO [HKEY_CLASSES_ROOT\Directory\shell\prompt\command]
>>"%Temp%.\hideActiveXwarning.reg" ECHO @="%_cmd% /k cd \"%%1\""
>>"%Temp%.\hideActiveXwarning.reg" ECHO.
:: Add Command Prompt Here for drives
>>"%Temp%.\hideActiveXwarning.reg" ECHO [HKEY_CLASSES_ROOT\Drive\shell\prompt]
>>"%Temp%.\hideActiveXwarning.reg" ECHO @="Command Prompt Here"
>>"%Temp%.\hideActiveXwarning.reg" ECHO.
>>"%Temp%.\hideActiveXwarning.reg" ECHO [HKEY_CLASSES_ROOT\Drive\shell\prompt\command]
>>"%Temp%.\hideActiveXwarning.reg" ECHO @="%_cmd% /k cd \"%%1\""
>>"%Temp%.\hideActiveXwarning.reg" ECHO.


:: Merge the temporary .REG file
:Merge
START /WAIT REGEDIT /S "%Temp%.\hideActiveXwarning.reg"

:: Delete the temporary .REG file
DEL "%Temp%.\hideActiveXwarning.reg"

:: Ready
GOTO End

:Syntax
ECHO.
ECHO setup.bat, for MS Office XP/2003


:: Clean up variables and quit
:End
SET _cmd=
SET _=