Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel97 - Disabling Macros

Status
Not open for further replies.
SHardy

SHardy

Programmer
May 9, 2001
231
GB
By default we have our options in Excel set to warn of potential macro viruses. I.e. show a warning if ANY macros are included in the file being opened. This then gives you an option to open the file with macros disabled.

Sometimes it is useful to open with the macros disabled. However, I have one particular file, where all the macros were written using VBA. But if I try to open this file with the macros disabled it tells me that the file contains version 4 Excel macros that cannot be disabled. There shouldn't be any. How can I look for Excel macros? Is it possible that some of the other functions, controls, etc could be making it think that there Excel macros? I have used many range names, data validation, etc.

Any help would be greatly appreciated.

Thank You.
 
Sort by date Sort by votes
It only lists those macros that I have written in the VB Editor. I don't think that this lists Excel macros. Is there any other way to find out what Excel macros are contained in the file?

Like I said previously though, I have written ONLY VBA macros. I have never even used the Excel macro language and have no knowledge of it at all. Cannot seem to find any useful documentation either. All I have been able to find out is that Excel97 can run Excel v4 macros, but they cannot be written in 97. Nothing about viewing/finding them.
 
Upvote 0 Downvote
The VBA Editor doesn't show the modules? You might want to check the Tools>References section in the VBE to see if there's any references that shouldn't be there.
 
Upvote 0 Downvote
Excel 4 macros were written on macro sheets (like worksheets). Go to Window and see if unhide is available. You just might find your macro sheets.

See this article too:


I did get rid of 4.0 macros once before for someone. I can't tell you how I did it (can't remember sh*t), but I'll certainly take a shot at one of your files if you email it. Brainbench MVP for Microsoft Word
techsupportgirl@home.com
 
Upvote 0 Downvote
Checked the VBE References. The only ones checked are:
1) Visual Basic for Applications
2) Microsoft Excel 8.0 Object Library
3) OLE Automation
4) Microsoft Forms 2.0 Object Library
5) Microsoft Office 8.0 Object Library

Nothing looks out of the ordinary there does it?

The only hidden item in the Windows menu is my PERSONAL.XLS. The only hidden sheets are thos that I have hidden myself.

The Knowledge Base link above refers to Excel2000. I'm using 97. Also the problem I'm having is NOT being able to disable macros, rather than them being disabled when they aren't supposed to be.

I could e-mail you the file, but it is rather big at the moment (4.3Mb). I'll make a copy and see if I can delete bits out of it. You never know, doing this might help me to track down where the problem is. Which e-mail address would you prefer I use? Your home address?
 
Upvote 0 Downvote
I can do that. Before I send it, I have started going through trying to pinpoint it. I have now deleted ALL code, but I still get the macro warning and the message about Excel 4 Macros. Wierd!
 
Upvote 0 Downvote
Right. I now have a file that has NO code, NO command buttons and NO hidden sheets/cells. Still the problem persists. Dreamboat, I shall zip it and mail it to you now.

Thanks for taking a look.
 
Upvote 0 Downvote
Dreamboat:

With regards to the stripped out file that I sent to you:

If I remove sheets "CES", "OES" & "AEAS" from the file and re-save then I don't get any macros reported. However, if I remove them from a full copy of the file (with code & buttons) I still get the error.

So there is a problem with these sheets, and also with the full file. I'll send a copy of the full file to yourself. I'll still make sure that nothing is hidden or protected though.
 
Upvote 0 Downvote
Dreamboat,

Just out of interest, I have seen you in the "experts-exchange" site. I have not been able to connect to their site for a few days now and was just wondering if it was a problem at my end or at their end. Have you been able to connect to them this week?

Thanks.
 
Upvote 0 Downvote
Darn, SH. For the life of me, I'm unable to figure out why it's doing this. That is one hellatious (sp?) file you've got there and, frankly, I'd consider rebuilding it anyway. If you've had it around this long, it's probably got other problems anyway. And have you considered an Access database for it instead?

I'm really sorry I couldn't help. There's just too many macros in it--I'd be looking forever to find something invalid. Have you had a serious VB coder look at it? You may want to consider that too.

Brainbench MVP for Microsoft Word
techsupportgirl@home.com
 
Upvote 0 Downvote
Thanks. Just got an e-mail from ee community support informing me that they are back.

Back to the Excel file, it is a bit of a sensitive and political issue with regards to this file. I, originally, wasn't going to have anything to do with it. I was then asked to provide support to the person who was building it. Ended up that I was re-building large chunks of it to reduce the amount of code, improve running times and improve efficiency. However, the original developer did seem to have it in his head that he was "top notch", and thus went on to changing bits after. I have since had to work with the mess, as the company are insistent that it's what they want, but putting through untold changes while trying not to change the overall function too much. I would've preferred to take it from scratch, or had even suggested the same as yourself right at the start of the project - use MS ACCESS. Would be far better suited to Access. But hey, company politics. What can you do? Instead I've just given them what they wanted, be it far from perfect.

Thanks for looking at this for me anyway. There shouldn't be any need for users to disable the code. I just noticed the problem when I needed to. I am just a bit worried about pontential problems caused by it. The only thing that I don't understand is that the whole project was developed within Excel97, and you CANNOT write old style Excel macros in this version.

If I do find out a cause or a solution then I will post it here for information.

Regards.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Is it possible to record macros in Outlook? 1
Replies
8
Views
2K
Andrzejek
Andrzejek
SS-WRD
  • Locked
  • Question
Word Macros 1
Replies
4
Views
120
macropod
macropod
atyrautony
  • Locked
  • Question
Recorded Word Macro, won't run again from Macros 1
Replies
3
Views
82
macropod
macropod
billheath
  • Locked
  • Question
Excel blocking my files and macros
Replies
2
Views
43
billheath
billheath
pendle666
  • Locked
  • Question
Excel 2016 macros - stop to ask for cell range 1
Replies
13
Views
102
pendle666
pendle666

Part and Inventory Search

Sponsor

Back
Top