Excel 2013 Active X Controls

Hi All, My spreadsheet has many active X controls. I am trying to stop the dialog box that asks me to trust and initialise active x controls.

In the Excel 2013 Trust Centre I have set the Active X control setting to 'Enable all controls without restrictions or prompting ...'

But each time I open the file the dialog box still appears.

Any advice on how to stop it appearing please - I think I have done what I should.

An aside is that I am shown a dialog box prompt rather than a message bar prompt that information on google suggests should be shown.
LVL 1
BunchilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:
0
BunchilAuthor Commented:
Hi Michael, Thanks for your response, I have tried placing the folder that contains the file into a trusted location but it has not stopped the dialog box that asks me to trust and initialise active x controls from showing each time I open the file

Bunchil
0
Michael FowlerSolutions ConsultantCommented:
Bunchil

By default Trusted Locations do not include subfolders.

Could you confirm that the option "Subfolders of this location are also trusted" or try placing the file directly in the trusted location.

Michael
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

BunchilAuthor Commented:
HI Michael

I can confirm that  the option ' Subfolders of this location are also trusted'

Bunchil
0
ded9Commented:
I hope you have restart the system after making changes to excel settings. Post some screenshot of the error in possible.



Ded9
0
BunchilAuthor Commented:
Hi Ded9 sorry for my delayed response. This is a summary of my situation, I hope you may be able to help.

My workbook was created in Excel 2003. The workbook is large, (99 Work Sheets) but work very well with Excel 2003.
We've upgraded to Office 2013 and when I open the file (xlsm) now, I see this warning shown in the graphic attached about active X controls
After I select OK and then, using the File menu and check for compatibility issues I am shown an extensive list of where there is a significant loss of functionality. In particular ‘One or more cells in this workbook contain data validation rules which refer to values on other worksheets. These data validation rules will not be saved.’
When I click 'Find', some cells that do indeed have validation rules are highlighted, but those rules are all on the same worksheet! We're using simple list-based validation, with some cells off to the side containing the valid values (for example, cell B4 has a List with Source "=$D$4:$E$4")
This makes no sense to me whatsoever. One, the workbook was created in Excel 2003, so obviously we couldn't implement a feature that doesn't exist. Secondly, the modifications we're making don't involve changing the validation rules at all. Thirdly, the complaint that Excel is making is incorrect! All of the rules are on the same worksheet as the target.
In the Excel 2013 Trust Centre I have set the Active X control setting to 'Enable all controls without restrictions or prompting ...' and the option ' Subfolders of this location are also trusted' is checked
I have placed the folder that contains the file into a trusted location but it has not stopped the dialog box that asks me to trust and initialise active x controls from showing each time I open the file.
I restarted the system after making changes to the excel system
I resaved the file as a 97-2003 workbook while using excel 2013 and went to an old computer and opened the document with Excel 2003 - the validations were untouched and the file opened properly.
0
ded9Commented:
Enable macro setting in excel settings...then try opening the file

Is it possible to share the excel file


Ded9
0
BunchilAuthor Commented:
Hi Ded9, Current macro settings are ' Disable all macros with notification' and the 'Trust access to the VBA project model' is checked.

All macros appear to be working properly but I did try 'Enable all macros' and re opened the work book but there was no change to the way it opened.

Re sharing the work book, The work book is a significant project for  a client of mine and is a significant basis for his consultancy business. This work book has been developed over more than 10 years. I know he will be concerned about risks for his IP so I will ask him about sharing the file with you.

What guarantees can you provide for him that no one else will have access to the file and that you will delete the file when we have finished working on it.

Thanks again for your help
Bunchil
0
Michael FowlerSolutions ConsultantCommented:
Bunchil

The following has been sourced from http://support.microsoft.com/kb/827742


This problem occurs when you try to open a document that contains ActiveX Controls that are marked as unsafe for initialization

This can be reolved by

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.
Note The available settings for UFIControls are 1 through 6. The most secure setting is 4. The default setting is 6, and the least secure setting is 1. For more information about the valid values for this DWORD Value, see the "Information about the UFI control settings for Active X components" section.
9.Quit Registry Editor

I hope this solves the error for you
0
ded9Commented:
Try Michael74 suggestion ..if the issue persist then contact the mods ...if you want then i will remote in via teamviewer and check. You can check my profile for my email address but first check with mods.

Can also try launching excel in safe mode  ...in run box type excel /safe then try opening the excel file.



Ded9
0
BunchilAuthor Commented:
Hi ded9 thank you again for your suggestion, I have tried that with no success. I was given a suggestion for registry change that has worked for my computer which is OK for me but it won't change the situation for other people who use the program. The change was:

open registry
under  HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA add a key named    Security
under HKEY_CURRENT_USER\SOFTWARE\Microsoft\Security  add a dword named     LoadControlsInForms

set HKEY_CURRENT_USER\SOFTWARE\Microsoft\Security\LoadControlsInForms  to the value 4  (although 1 also works).

I do not understand why the file works properly in excel 2003 but when I open it in Excel 2007 and use the 'Save As' option to save it as an Excel 97 - 2003 file

Compatibility issues reported are:
This workbook contains data in cells outside of the row and column limit of the selected file format. Data beyond 256 (IV) columns by 65,536 rows will not be saved. Formula references to data in this region will return a #REF! error.

Some formulas in this workbook are linked to other workbooks that are closed. When these formulas are recalculated in earlier versions of Excel without opening the linked workbooks, characters beyond the 255-character limit cannot be returned.


When I open the file in Excel 2013 and use the 'Save As' option to save it as an Excel 97 - 2003 file

Compatibility issues reported are:

 One or more cells in this workbook contain data validation rules which refer to values on other worksheets. These data validation rules will not be saved.

Some formulas in this workbook are linked to other workbooks that are closed. When these formulas are recalculated in earlier versions of Excel without opening the linked workbooks, characters beyond the 255-character limit cannot be returned.

Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available.


In each case all I do is open the file and the initiate the 'Save As' option.

On computers where the registry has not been changed as described above I continue to get the request to trust and initialise activeX controls.

Thank you for any help you are able to provide  - I am getting balder from hair pulling by the houre
0
BunchilAuthor Commented:
Hi All, I found some VBA code that I have edited slightly and added to a spreadsheet module that I can provide to my clients to edit their registry to fix the ActiveX problem I have. The initial code came from: http://vba-corner.livejournal.com/3054.html.

However I need one more piece of code to, from within Excel VBA, export the registry to create a backup before a change is made.

Does anyone know of VBA code that can be used to export the complete registry so a user will have a backup vertsion of their registry before it is changed.

The code I can use to change the registry to fix the ActiveX problem is:
Function RegKeyExists(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'try to read the registry key
  myWS.RegRead i_RegKey
  'key was found
  RegKeyExists = True
  Exit Function
 
ErrorHandler:
  'key was not found
  RegKeyExists = False
End Function

Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function

Sub RegKeySave(i_RegKey As String, _
               i_Value As String, _
      Optional i_Type As String = "REG_DWORD")
Dim myWS As Object

  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'write registry key
  myWS.RegWrite i_RegKey, i_Value, i_Type

End Sub

Sub TestRegistry()
Dim myRegKey As String

  'get registry key to work with
 
  myRegKey = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\Security\LoadControlsInForms"

 
  'check if key exists
  If RegKeyExists(myRegKey) = True Then
    'key exists, read it
    myValue = RegKeyRead(myRegKey)
    'display result and ask if it should be changed

    RegKeySave myRegKey, 4
    Else
    RegKeySave myRegKey, 4
End If


End Sub
0
BunchilAuthor Commented:
Sorry for my lack of response, my problem has been solved by changing a value in the registry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BunchilAuthor Commented:
The code I obtained from another site that checks the value of the registry and then changes it is:
Function RegKeyExists(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'try to read the registry key
  myWS.RegRead i_RegKey
  'key was found
  RegKeyExists = True
  Exit Function
 
ErrorHandler:
  'key was not found
  RegKeyExists = False
End Function

Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function

Sub RegKeySave(i_RegKey As String, _
               i_Value As String, _
      Optional i_Type As String = "REG_DWORD")
Dim myWS As Object

  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'write registry key
  myWS.RegWrite i_RegKey, i_Value, i_Type

End Sub

Sub TestRegistry()
Dim myRegKey As String

  'get registry key to work with
 
  myRegKey = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\Security\LoadControlsInForms"

 
  'check if key exists
  If RegKeyExists(myRegKey) = True Then
    'key exists, read it
    myValue = RegKeyRead(myRegKey)
    'display result and ask if it should be changed

    RegKeySave myRegKey, 4
    Else
    RegKeySave myRegKey, 4
End If


End Sub
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.