PDA

View Full Version : Worksheet Sheet ActiveX Controls Command Buttons Stopped Don't Work Working



SamT
12-23-2014, 06:14 PM
Will leave this post at the top of this forum for a few weeks or until Microsoft Fixes the problem.

all the following is thanks to Yi at http://www.excelclout.com/microsoft-update-breaks-excel-activex-controls-fix/

Imperfect Solution For Developers: VBA Code

If you're a developer who simply cannot reach into all your clients' computers, you can insert the following VBA code into the spreadsheet you're working on or distributing. The VBA code is essentially the code behind the tool I created that attempts to rename the MSForms.exd files. Add the VBA code to your spreadsheets, and your spreadsheets will be "immune" to the Microsoft Updates.
Copy and paste the following VBA code into any module in the spreadsheet.



Public Sub RenameMSFormsFiles()
Const tempFileName As String = "MSForms - Copy.exd"
Const msFormsFileName As String = "MSForms.exd"
On Error Resume Next

'Try to rename the C:\Users\[user.name]\AppData\Local\Temp\Excel8.0\MSForms.exd file
RenameFile Environ("TEMP") & "\Excel8.0\" & msFormsFileName, Environ("TEMP") & "\Excel8.0\" & tempFileName
'Try to rename the C:\Users\[user.name]\AppData\Local\Temp\VBE\MSForms.exd file
RenameFile Environ("TEMP") & "\VBE\" & msFormsFileName, Environ("TEMP") & "\VBE\" & tempFileName
End Sub


Private Sub RenameFile(fromFilePath As String, toFilePath As String)
If CheckFileExist(fromFilePath) Then
DeleteFile toFilePath
Name fromFilePath As toFilePath
End If
End Sub

Private Function CheckFileExist(path As String) As Boolean
CheckFileExist = (Dir(path) <> "")
End Function


Private Sub DeleteFile(path As String)
If CheckFileExist(path) Then
SetAttr path, vbNormal
Kill path
End If
End Sub



Call the RenameMSFormsFiles subroutine at the very beginning of the Workbook_Open event.

Private Sub Workbook_Open()
RenameMSFormsFiles
End Sub



The spreadsheet will try to rename the MSForms.exd files when it opens. Obviously, this is not a perfect fix:
1. The affected user will still experience ActiveX control errors when running the VBA code the very first time opening the spreadsheet. Only after executing the VBA code once and restarting Excel, the issue is fixed. Normally when a user encounters a broken spreadsheet, the knee-jerk reaction is to close Excel and try to open the spreadsheet again. :)
2. The MSForms.exd files are renamed every time the spreadsheet opens, even when there's no problem with the MSForms.exd files. But the spreadsheet will work just fine regardless whether the machine running the spreadsheet has applied the Microsoft update or not.
At least for now, developers can continue to distribute their work with this workaround until Microsoft releases a fix.

___________________________________________________________________________ _____________

I cannot comment on the code above as I do not use Excel > 2003. SamT

snb
12-24-2014, 06:11 AM
You can create a batchfile.
After closing all office applications you can run the batchfile in Explorer.
Adapt the filename - G:\OF\exd_snb.bat - in the example to your own situation.


Sub M_snb()
CreateObject("scripting.filesystemobject").CreateTextFile("G:\OF\exd_snb.bat").write "cmd /c del """ & Split(Environ(27), "=")(1) & "\*.exd"" /s/p"
End Sub