Delete or Disable Macro Assigned to a Specific Sheet When Ran

[Ottsel]

I have a format macro that is assigned to a sheet that helps with case sensitive material. The problem is once the sheet is done and the formatting is no longer required we process the sheet to be turned into a order, which is done from a macro. The macro is encountering an issue where that sheet macro is causing an issue, but simply delete it from the sheet fixes the issue. My problem is that other people who use this workbook do not understand how to locate the VBA screen and to locate the sheet, so I was hoping to find a way to have a macro that will either delete the code within the active sheet OR disable it within the active sheet.

Any and all help is greatly appreciated.

 

[AC PORTA VIA]

see if this can help you
from Deleting VBA Code in a Copied Worksheet

Sub delete_the_code_within_the_active_sheet()


wsName = ActiveSheet.CodeName
With ThisWorkbook.VBProject.VBComponents(wsName).CodeModule
    .DeleteLines 1, .CountOfLines
End With
End Sub

 

[Peter_SSs]

Sounds like the first code is an 'Event' code (eg WorkSheet_Change) and the second macro is triggering the first code. If that is the case, you could stop that trigger by the second code as follows.

Sub SecondMacro()
  Application.EnableEvents = False
  'Rest of SecondMacro code goes here
  Application.EnableEvents = False
End Sub

 

[Ottsel]

Sounds like the first code is an 'Event' code (eg WorkSheet_Change) and the second macro is triggering the first code. If that is the case, you could stop that trigger by the second code as follows.

Sub SecondMacro()
  Application.EnableEvents = False
  'Rest of SecondMacro code goes here
  Application.EnableEvents = False
End Sub

Correct. I should of mentioned that to within my question.

This worked nicely, but I must ask: is it required to have

  Application.EnableEvents = False

entered at the beginning AND at the end of the code? Was just wondering the reason behind it, as I do know it's good practice to re-enable some features.

Example being

Application.DisplayAlerts = False 

'input code here

Application.DisplayAlerts = True

then at end of your code to change this back to true to avoid any problems down the road.

 

[Peter_SSs]

I think good practice. Otherwise you (or somebody else) could easily come back to the code at a later time and add more at the end and forget that events were disabled at the beginning.
Disregard the above - see next post

 

[Peter_SSs]

I didn't read my own post well enough, or yours regarding both EnableEvents lines being False.
Definitely should have the last one as True! I copy/pasted the first one and forgot to edit.
Sorry if I have misled you.

So ..

Sub SecondMacro()
  Application.EnableEvents = False
  'Rest of SecondMacro code goes here
  Application.EnableEvents = True
End Sub

 

[Ottsel]

I didn't read my own post well enough, or yours regarding both EnableEvents lines being False.
Definitely should have the last one as True! I copy/pasted the first one and forgot to edit.
Sorry if I have misled you.

So ..

Sub SecondMacro()
  Application.EnableEvents = False
  'Rest of SecondMacro code goes here
  Application.EnableEvents = True
End Sub

No worries. Thank you for clarifying and all your help Peter. I thought it was false, then true, but still pretty new with VBA, so wanted to confirm.