0

I made a workbook which we are using with multiple people. In the workbook i have a macro which places some value in column A when a certain range changes.

This all works well. But somethimes when people are opening the file, the macro seems disabled. This happens even when content is enabled and macro's are enabled.

I tried to enable events, calculation etc when files get opened, but if the macro's are disabled this wil not work ofcourse.

How can i fix this and why is this happening?

Some of my code

Private Sub SyncProjectplanning()
Run "OptimizerON"
Run "PublicFoldersAndFiles"
Run "PublicProjectInfo"
Run "ProjectScreenupdate"

'Controle voor updates in fase informatie
If ActiveWorkbook.Sheets("Planning").Range("A7") = 1 Then
    Run "SyncToBureauplanner"
    Run "SyncPhaseToEmployeplanners"
End If

Run "SyncToEmployeplanners"
Run "OptimizerOFF"

Exit Sub

End Sub
Jelle van der Heijden
  • 77
  • 7
  • `How can i fix this` You cannot. `why is this happening?` Security reasons. If I am not wrong, this question has been asked so many times... – Siddharth Rout Sep 07 '20 at 07:02
  • https://stackoverflow.com/questions/9421843/excel-vba-enabling-macro-settings – Siddharth Rout Sep 07 '20 at 07:04
  • If you would like get it work, you have to write Excel add-in. – Maciej Los Sep 07 '20 at 07:06
  • `This happens even when content is enabled and macro's are enabled.` If macros are enabled and still you face this issue then then most likely culprit is *events*. Probably, the events were switched off and were not switched on correctly? Possible to see the code? – Siddharth Rout Sep 07 '20 at 07:11
  • You can sign your VBA code with a trusted certificate. If it is only inside your company you can use a self-signed certificate (that you trust once in all computers, or distribute through your server policies). If it is not company internal you need to buy a certificate from a trusted certificate authority. Trusted VBA code can then automatically be accepted by Excel and the users don't have to activate them. – Pᴇʜ Sep 07 '20 at 07:30
  • Thank you for al the replies @Pᴇʜ how can I sign my macro's and will a Excel add-in help in this case as MacieJ is suggesting? – Jelle van der Heijden Sep 07 '20 at 07:33
  • @SiddharthRout I checked my code and its getting switched on and off correctly. I will add a piece of code in the main post. – Jelle van der Heijden Sep 07 '20 at 07:35
  • @JellevanderHeijden see the official info: [Digitally sign your macro project](https://support.microsoft.com/en-us/office/digitally-sign-your-macro-project-956e9cc8-bbf6-4365-8bfa-98505ecd1c01) – Pᴇʜ Sep 07 '20 at 07:37
  • @Pᴇʜ thank you for the information. But why is it sometimes working fine and sometimes blocking the code then? Shoud it not always be blocked when it is not signed? – Jelle van der Heijden Sep 07 '20 at 07:39
  • No, it depends on the users security restrictions. If the user clicks the "allow macros" button when starting your workbook then it works. If the user does not, it doesn't run any macros. Singned macros can automatically be trusted in Excel. Check the security properties in the Trust Center of the Excel options. – Pᴇʜ Sep 07 '20 at 07:41
  • 1
    No you are not switching off the events correctly. You may want to see [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) where I have explained how to correctly work with events in such a case. See point number **3** – Siddharth Rout Sep 07 '20 at 07:56

0 Answers0