Macros randomly failing when file opened

Hi all,

I have an annoying problem. I have built an excel based database of clients and employees for my company. Each client and employee has their own file to avoid monster file sizes. There are a number of ways to load a file all run by macros. The problem I am having is that approximately 50% of the time when it opens the client record and closes the main file, the macros in the client file will not work. It just says that the macro cannot be found or macros may be disabled. I can close the file, not saving changes and then re-open the same record using the exact same method as before and all macros work fine. Anyone got any ideas as its becoming a real pain!!

Thanks in advace!
James

Hello James,

Welcome to the Forum!

You don't say if these files are shared or not, just that each client and employee has their own file. Without seeing the macro code you are using, it is impossible to accurately diagnose the cause of the problem.

Sincerely,
Leith Ross

Hi Leith,

Thanks for the reply. Firstly, the files are not shared. They are all accessed and stored on the same computer. the reason each client/employee has their own file is each one has 6 sheets dedicated to them and that would obviously cause problems if you had 100 clients or more all in the same file.

As I said there are many ways a client file can be opened from the main file. All of them use the same coding and the same problem happens completely at random. This is the coding I use.

Please Login or Register  to view this content.

I still don't think this is a coding issue but I am all out of ideas.
I'ts getting really frustrating now so ANY help would be greatly appreciated!!

One thing that may be worth noting is that if I load a file and the marcos fail, I close the file go back and re-load it in the same way and it works fine. I've never had the same file fail twice in a row.

Thank you!
James

Hi Rob,

I have replaced the code with your suggestion, and so far so good. I've tested it with 10 different client files and had no issues. I still don't understand how it relates to macro security but one thing I have come to learn with computers is you don't NEED to know how something works... just that it DOES work!!

Cheers for the help!
James

it DID work! but it was just teasing me! I'm only opening a single file at a time. The fact that it happens seemingly at random suggests to me that it MIGHT be a bug in excel rather than my programming. Thanks for the help anyway Rob!

James