Advice: Newbie to VB.. auto run code worked in XL 97 but not in 2003

[MelanieB]

One of our users has a very old code that works great in Excel 97, but not in Excel 2003. It is set to run when the workbook opens. It's a menu. I do not know code, but am hoping that if I hang around her long enough and ask enough questions I'll get over this hump of illiteracy!

He said this code was written waaaaay back for Lotus. It looks to me like embedded text that is on the worksheet and somehow he has it open a customized menu for him to select macros that are actual macros in the workbook. I don't understand how it worked in the first place. It isn't located in Tools, Macros.

Anyone game to look at it? I'm attaching it. When you open the workbook in XL 97, a menu dialog box opens to allow you to select menu items. The "macro" is located at R2 in the workbook.

[Richie(UK)]

Hi Melanie,

Welcome to the forum :)

OK, based upon the zipped file that you attached we have:

1. No code in either 'Sheet1' or 'ThisWorkbook'.
2. Module1 is empty.
3. Modules 2, 3 and 4 each contain a single routine related to printing.

Now in order for a routine to run automatically when the workbook is opened we need either :
a) A Workbook_Open event routine (newer code), or
b) An Auto_Open routine (older code).

The attached workbook contains neither of these nor any menu creation routines (or not in the VBE anyway). I'm afraid I'm not familiar with the technique used in this workbook. Are you looking to get the existing method working in 2003 or just for a method of creating a menu to access the routines that will load when the workbook is opened?

A simple menu creation routine is outlined here:
http://vbaexpress.com/forum/showthread.php?t=50

Let us know how you want to proceed.

[Mike_R]

I have Excel 2002 and nothing hapens... :no

It looks like your Lotus-style macro is *roughly* within the Range("R2:AE115"). The Sheet name is even "A", which is Lotus's version af "Sheet1". It's all kind of quant and vaguely familiar to me. Unfortunately, I don't remember how those old Lotus macros worked. They are basically a series of recorded keystrokes and, yes, Excel did put in a capablity to run them, back in the day...

I think you're going to have to redo these menus using a more modern approach, and Richie's code is a great way to learn how to do it. Give it a read, take your first shot at it, and we can help talk you through it if/when you get stuck. :)

-- Mike

[Insomniac]

The Workbook certainly works fine for me with xl97, I only have a vague knowledge of this technique, but this link may be of use :http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q128/1/85.ASP&NoWebContent=1

Download the Macrofun exe for a comprehensive help file.

Interesting is that is you try to disable macros you get the message:

"This workbook contains a type of macro (Microsoft Excel version 4.0 macro) that cannot be disabled. There may be viruses in these macros.

If you are sure this is from a trusted source, click Yes. Open workbook."



I can generate this message myself when using xl4 named formulas, but dont have any real explanation how it all works.