temporarily disable a sub

[rickblunt]

Greetings, I am using the following sub to save a single WS from a WB onto a network drive and it works flawlessly.

Sub ExportElReport()


  Dim wb As Workbook
  
  Sheets("Emergency Lighting Inspection").Copy
  Set wb = ActiveWorkbook
  With wb
    application.DisplayAlerts = False
    .SaveAs _
         "N:\Facilities\DeptData\EH&S\Compliance and EHS\Facilities Inspections\Emergency Lighting\Emergency Lighting Report  " _
          & Format(Now, "yy_mmdd") & ".xlsx", FileFormat:=51
   .Close True
   application.DisplayAlerts = True
  End With
End Sub

I wanted to add a userform toolbar to my WS to give the user a couple of buttons to perform quick functions. (I know that I can embed buttons on the WS, but if I make it a tool bar then the button are always on top no matter where you scroll through the WS). So I added the following subs to the WS code.

Private Sub Worksheet_Activate()


FeReportControls.Show vbModeless


With ElReportControls
  .StartUpPosition = 0
  .Left = application.Left + (0.09 * application.Width) - (0.5 * .Width)
  .Top = application.Top + (0.8 * application.Height) - (0.5 * .Height)
  .Show
End With
End Sub




Private Sub Worksheet_Deactivate()


Unload ElReportControls


End Sub

The issue that I am having now though is that when it saves the code crashes ("object required"). I believe that this is because as a function of the SaveAs it is opening the new WS which calling for the userform which it cannot see since that is stored on the original WB. Is there a way to disable the worksheet activate/deactivate subs during the initial export sub?

Thank you for any advice.

 

[dmt32]

Hi,
You can disable all workbook / worksheet Events by setting EnableEvents =False

Example

Sub MySub()
 
Application.EnableEvents = False
'your code
 
 
Application.EnableEvents = True
End Sub

Note: you must explicitly set EnableEvents = True before your code terminates otherwise event code will not trigger.

Dave.

 

[rickblunt]

Thanks for replying Dave, I knew about that string, but I figured if I used this it would stop the SaveAs event as well. Is this not correct?

 

[dmt32]

Thanks for replying Dave, I knew about that string, but I figured if I used this it would stop the SaveAs event as well. Is this not correct?

It should disable the BeforeSave event but you should still be able to use SaveAs method in your code.

Dave

 

[rickblunt]

Thanks again Dave, it worked great - I should have tried it just to see before I posted it up - doh!.... I am guessing that I do turn off "everything", but as soon as I call the "SaveAs" back then it is going to enable that part? Thanks again for my continuing education

 

[dmt32]

Thanks again Dave, it worked great - I should have tried it just to see before I posted it up - doh!.... I am guessing that I do turn off "everything", but as soon as I call the "SaveAs" back then it is going to enable that part? Thanks again for my continuing education

When your code sets EnableEvents = False then Events in your workbook will stay disabled until your code sets them True.

No worries - I have done & said some daft things when responding to requests on the board. I guess we all do it.
Glad issue resolved.

Dave