[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4623
  • Last Modified:

Excel for iMac wont run macros.

New iMac with Excel 2011 opens spreadsheet, but macros will not run. Work was created on Excel for Microsoft OS.

After opening, there is a splash screen that reads "This workbook contains macros. Do you want to disable macros before opening the file" We click Enable Macros. The file opens successfully and the data is available to view. New data can be input in the workbook, but if the macro button is pressed we get the "Compile error. Cant find project or library" message.

The macros also give us issues on Windows OS installs; however we have found that the fix is to add the MSCOMCT2.OCX file to the appropriate sys32 folder. We also need to be sure there is a valid date in the 'last record' created. The fix on the IOS system has not been as simple.

The iMac code that is first displayed in the debugger is below. The workbook is attached, but all client info has been deleted. This may have some effect on function of the macro, but it is sensitive information.


Compile error. Cant find project or library.

Private Sub UserForm_initialize()
Dim b As Integer

DTPicker1 = Date
DTPicker3 = Date
DTPicker4 = Date

'Sheet3.Visible = xlSheetVisible '(show sheet to make changes)
'Sheet4.Visible = xlSheetVisible '(show sheet to make changes)

If WorksheetFunction.CountA(Cells) > 0 Then
    M = ActiveSheet.Cells.Find(what:="*", after:=[A1], _
    searchorder:=xlByRows, _
    searchdirection:=xlPrevious).Row
End If
0
2ndOf3
Asked:
2ndOf3
  • 3
  • 3
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
You may be missing the Active X control required

Note: The DateTimePicker control is part of a group of ActiveX controls that are found in the MSCOMCT2.OCX file. To use the DateTimePicker control in your application, you must add the MSCOMCT2.OCX file to the project. When distributing your application, install the MSCOMCT2.OCX file in the user's Microsoft Windows System or System32 directory. For more information on how to add an ActiveX control to a project, see "Adding Controls to a Project" in the Programmer's Guide.

http://msdn.microsoft.com/en-us/library/aa276346%28v=vs.60%29.aspx
0
 
tdlewisCommented:
The DLL that you have to install adds "Microsoft Windows Common Controls" to the system and allows ActiveX controls to be used on forms loaded by your macro. Once upon a time when there was an Internet Explorer for Mac, Microsoft supported ActiveX, but IE for Mac was discontinued in 2003. As far as I can tell, there is no way to load an ActiveX control on Mac. Your next best bet is to refactor the macro so that it does not require an ActiveX control.
0
 
2ndOf3Author Commented:
TDLEWIS

I will take the 'next best option.' How can i get that done? Can you, or anyone on this site, help with that?

Can anyone redesign the attatched spreadsheet to work on Mac and newer Windows machines?
Macro-iMac-issue.xlsm
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
tdlewisCommented:
2ndOf3, try the attached spreadsheet on your Mac and see if it works as expected.

I did the following:
1. Open the spreadsheet in Excel on Windows
2. Open the Visual Basic Editor
3. Choose Tools-References
4. Uncheck Microsoft Windows Common Controls
5. Save the workbook
Macro-iMac-issue.xlsm
0
 
2ndOf3Author Commented:
TDLEWIS

This appears to have solved it! I don’t know enough about the content of the sheet, so I will have a few users try it out over the next few days. We no longer get the Macro error debugger, so that says a lot. But I’m not sure that the functionality is intact until I hear back from the users.

Thanks for your effort, ill happily accept as solution as soon as I know it is working.
0
 
tdlewisCommented:
As a bonus, if it works you will no longer need to add the MSCOMCT2.OCX file on Windows computers.
0
 
2ndOf3Author Commented:
So far so good! Obviously the Windows Common Controls are unnecessary in the newer version Excel product. The macro is running fine without it.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now