Migrate Excel 4 macro file to 2010

perp1exed

New Member
Joined
Oct 28, 2011
Messages
20
Hi,

In the process of this I had one problem... the Excel 4 sheet macros have commands that manipulate the menu system. Since 2010 uses ribbon these will obviously no longer work.

So I removed those macro lines that touched the menu system.

There is one macro which calls the File Open dialog box.

The Macro is called OpenFile and its contents are:

=OPEN?()

Now, when I go to macros and manually Run this it works and opens the dialog.

But when I try to assign this macro to the onAction of a custom ribbon button I get an error Sub or Function not defined.

Is there a workaround/fix for this? Why can my ribbons onAction event not fire the macro when I can do it manually?

thanks,

KS
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
You probably need something like

Code:
Dim fName As Variant
fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fName = False Then Exit Sub
Workbooks.Open Filename:=fName
 

perp1exed

New Member
Joined
Oct 28, 2011
Messages
20
@Andrew

Yes thank you, this does work.

Maybe the solution then is to replace parts of the Excel 4 macro sheet functions that no longer work in 2010.

What are your thoughts?

thanks.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
@Andrew

Yes thank you, this does work.

Maybe the solution then is to replace parts of the Excel 4 macro sheet functions that no longer work in 2010.

What are your thoughts?

thanks.
I'd rewrite the lot, but maybe that's too big a job.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If you are using the CustomUI.xml to add the ribbon buttons, then the onAction is actually a callback that requires a particular signature, not a macro. You should however be able to call your macro from the callback.
 

perp1exed

New Member
Joined
Oct 28, 2011
Messages
20
@rorya,

I am using the following:

Private Sub Opener(ByVal control As IRibbonControl) <------onAction is Opener and here is signature of callback
OpenFile <---- macro which opens dialog
End Sub

This does not work for me.

thanks.
 

perp1exed

New Member
Joined
Oct 28, 2011
Messages
20
@rorya, @Andrew,

Actually, I think the ribbon button functions do not like calling any of these Excel 4 macros. I get the same error for another macro which is not menu related.

Maybe a rewrite it is ugghhh!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Have you tried:
Code:
Application.run "openfile"
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,746
Members
406,495
Latest member
Arlind Elezi

This Week's Hot Topics

Top