Please tell me I can stop this Excel message

Factual Questions
1

So we’ve got a template where the Macros don’t work on Macs. Long story. We want to be able to have Mac users do data entry in the worksheet anyway and then send it to PC users who can enable the Macros and take it from there. So far so good. The template is opened in Excel (2004 in this case) and Disable Macros, yes, we’re sure.

The trouble is, when data is entered in a cell and one tabs to the next cell, the following message comes up. “This document was opened with Macros disabled. If you wish to Enable Macros, you should close and re-open the document, choosing the ‘Enable Macros’ option.” EVERY TIME. Once would be fine, I know how important Macros are.

Is there any way to “disable” this message from appearing every. single. time. ? I’ve looked through Tools and Preferences and haven’t found anything.

Thanks!

2

Simplest solution I can see is giving the Mac users a copy of just the worksheet(s) they need (in a workbook that does not have macros attached to it), and write the PC users a macro that will import the data from that workbook into the one with the macros. No macros should mean no message. Sorry it’s just a workaround, but I don’t know of any other way to avoid that message. Disclaimer: I’ve never used Excel on a Mac.

3

Excel 2004 | Preferences | Security tab, disable “Warn before opening a file that contains Macros.” This is separate from the MVP alert that you disable on Open, and should prevent it.

4

There is a better way to do this, which is to rewrite the macros to recognize the operating system and set a systemwide flag to shut off the particular offending macros if it is running on a Mac.

For example, this simple line in a macro will display the name of the operating system.


MsgBox "Microsoft Excel is using " & Application.OperatingSystem

In your case, insert a Sub with the exact name


Sub auto_open()
    ... (further statements here)
End Sub

in the workbook. When the workbook is opened, the “auto_open” subroutine will run. You’ll want something like (WARNING - UNTESTED)


PUBLIC GoodSystemFlag = TRUE       'must be the first line in the macro sheet

with a macro somewhere in the code like


Sub auto_open() 
    if Left(Application.OperatingSystem,3) = "Mac" then GoodSystemFlag = FALSE
End Sub

Then wrap the macro that causes problems with a test for the system



Sub SomeMacro()
    If GoodSystemFlag then

        ... entire macro contents goes here ...

    End If
End Sub

If it’s a Mac, the macro will be skipped.

Alternatively, you can leave out the setting of a flag as shown above, and have it test the system each time the macro runs, like this:



Sub SomeMacro()
    If Left(Application.OperatingSystem,3)<>"Mac" then

        ... entire macro contents goes here ...

    End If
End Sub

Questions? Askem, I’ll watch the thread.

w.

5

I suddenly feel deeply, deeply inadequate.

6

That results in no message about Macros, and them being enabled, which results in a different error message each time data is entered, since the Mac doesn’t like them.

This has all become moot because even if I disable the Macros and suffer through the message with each data entry item, when the file is saved and sent to the PC user, the file craps out on compiling something in one of the Macros.

I think we’re down to data entry in a non-Macroed sheet on the Mac, and cutting and pasting into the PC’s macroed template. :frowning:

7

You can’t possibly mean that I gave you the closest thing to a workable answer.

8

intention, the problem with that is the VB layer in Mac XL is not as robust as the one in WinOffice. I don’t think GoodSystemFlag will work.

But my suggestion didn’t work either, so what do I know? :slight_smile:

9

Morbo, thanks for the response. I just tested it in a new spreadsheet on my my machine (Mac, Office 2004), and the auto_open macro set the public GoodSystemFlag with no problems.

However, for someone without much coding experience, it would be easier to just wrap the macros in question in a test for the operating system, viz:

Original macro


Sub SomeMacro()

        ... entire macro contents are here ...

End Sub

“Wrapped” macro, with new lines shown in blue:


Sub SomeMacro()
    If Left(Application.OperatingSystem,3)<>"Mac" then 

        ... entire macro contents are here ...

    End If
End Sub

That way, the code will only run if the machine is not a Mac.

My best to everyone,

w.

PS - To the person who said they feel deeply inadequate, I wrote my first computer program in 1963, bought my first computer in 1982, bought my first Mac in 1984, and started using Excel when MS came out with the very first one, before it even had a version number. Give it time …

10

:stuck_out_tongue: But yours was quite sophisticated compared to ours, which will have to be cutting and pasting.

11

Good to know. MacPPT does not support auto_open, hence my ignorance. [sub](I’d be curious to know if I perhaps know you IRL. MSFT PPTDev since 1992, Macbu PPTDev since 1996).[/sub]

12

Possible but doubtful. I’m a generalist. My last few jobs going backwards are:

Present - Design and construction of a 1 room addition to a house in Hawaii

Previous - Graphic artist, production of final drawings for an island development master plan

Last summer - Sport salmon fishing guide, Kenai River, Alaska

Before that - Construction Manager, resort development in Fiji

I’m leaving in a couple of weeks to be the Accounting Department Manager for a company in the Solomon Islands … go figure.

Like I said, I’m a generalist, including a hard-core programmer, but I don’t work at just programming very often.

w.

13

Huh. And I’ve been doing the exact same thing for the exact same product for the exact same company in the exact same office…for 15 years.

::defenestrates::

14

Off topic, I know, but when I was a kid, I saw that you could either go for security or for adventure … I chose adventure, because it fit me, but I wouldn’t recommend it for everyone. There’s lots of uncertainty, no assurance of making the next house/car/whatever payment, most women find it difficult to understand (You’re going where? When? For how long? Hope you don’t think you’re coming back, sucka!), short term work is not all that common, long-term planning is … interesting … and the like.

But heck, I figured I wanted to retire early … and often … so my life has been a succession of short-term jobs (from one week to five years) with all too short periods of retirement in between. Among a host of other jobs, I’ve worked as a commercial fisherman in the Bering Sea, an energy consultant in Africa, a jeweler in California, a psychotherapist in Hawaii, a stained-glass artisan in Berkeley, and a musician in New York.

If that kind of action sounds attractive to you, hey, go for it … you have nothing to lose but your life, and you’re likely to lose that eventually in any case. But if you do, just remember that in my kind of life, shelter, meals and the like are sometimes optional …

My best to everyone,

w.

PS - don’t believe Jimmy Buffet when he sings

“Yes I am a pirate, 200 years too late”

The world is large and unknown and full of adventure, even in the 21st century.

15

Get a room, you two.

:smiley:

16

It’s true. I’ve realized how much I am not a risktaker. I get frustrated by the dull routine but also find comfort in it since I know I would be literally sick if I had too much risk and uncertainty. Is the thrill of new adventures worth the upheaval? I don’t know if I’ll ever find out.

17

Actually, Morbo, I’m thinking you might know someone on my Livejournal FL.