Macros not running (Excel 2000)

Topic

New Reply

I received a spreadsheet from someone and the macros do not run. I am not prompted to enable or disable macros when I open the file. Perhaps this is a function I do not have enabled?

Can anyone see the problem with this spreadsheet?

Thanks…Susan

Reply | Quote

Replies

You probably have your security settings set to high.

Go to Tools, Macros, Security and if set to High change to Medium.

Andrew C

Reply | Quote

That worked perfect. Thank you very much.

Reply | Quote

I am still having problems with this file on Windows 2000 stations. The security is set to medium, references are set correctly and I still get a “compile error, cannot find project or library”. I uninstalled and reinstalled office with all the options. In the references was a “MISSING: Microsoft Windows Common Control2-6” so installed windows 2000 sp3. This still did not resolve the problem.

Does anyone have any ideas?

Reply | Quote

Microsoft Windows Common Controls-2 6.0 are not part of a standard Office or Windows installation, as far as I know. These controls come with Visual Studio 6 (VB6 etc.), and with Office Developer Edition. So you’re bound to run into trouble on some PC’s, unless you make an installation package that installs these controls (in this case, the file is MSCOMT2.OCX).

Reply | Quote

I did try to copy this file to the system32 directory, and I still get the compile error. However, when I save this file on a windows 98 machine, it opens fine on a windows 2000 pc.

I will try to find the visual studio 6 software and install it on the windows 2000 station. Thanks.

Reply | Quote

You can’t just copy such a file to another machine; it has to be registered too. That’s what installation programs do for you. There may be tricks to do it, but I can’t help you with that…

Reply | Quote

You need to register the control on th etarget system. Windows has a utility called Regsvr32 to do just that.

Run the following command :

Regsvr32 /s C:WindowsSystem32MSCOMCT2.OCX

I attach a workbook that will on opening install the control for you provided that a copy of Mscomct2.ocx is included in the same folder as the workbook. It copies the control to the system directory and then registers the control on the target computer..

The code is as follows :

Private Declare Function GetSystemDirectory Lib “kernel32” _
Alias “GetSystemDirectoryA” ( _
ByVal lpBuffer As String, _
ByVal nSize As Long) As Long

Function SysDir()
Dim sSave As String, Ret As Long
sSave = Space(255)
Ret = GetSystemDirectory(sSave, 255)
sSave = Left$(sSave, Ret)
SysDir = sSave
End Function

Private Sub Workbook_Open()
Dim wshShell, oFSO, sf
Dim strInstallFrom As String
strInstallFrom = ThisWorkbook.Path & “”
Set wshShell = CreateObject(“WScript.Shell”)
Set oFSO = CreateObject(“Scripting.FileSystemObject”)
On Error Resume Next
oFSO.CopyFile strInstallFrom & “Mscomct2*”, SysDir
On Error GoTo 0
Shell “Regsvr32 /s ” & SysDir & “Mscomct2.ocx”
Set sf = Nothing
Set wshShell = Nothing
Set oFSO = Nothing
MsgBox “Mscomct2.ocx installed”, vbInformation
End Sub

Andrew C

Reply | Quote

This worked perfect. Thank you very much.

Reply | Quote