Using code to delete code

Question

I have googled this and found multiple solutions for this but none of them have worked for me. I am inclined to believe this is because of my limited knowledge and ability when it comes to programming in VBA in Excel. If this is a repeat question of questions already posted here I apologize.

The question:

I have a code that executes on open of Excel Workbook. The code that executes saves the workbook as a new workbook under the name of a value located in a cell. I need the code that executes on open of Excel Workbook not to run on the new copy that has been created. The best way that I can think of to go about this would be to have a code that deletes part of or all of the code that executes on open of Excel Workbook. I have seen solutions for this before, I know that a solution exists but I do not know how to properly implement the code into my program so it works, I don't even know if the code I found will work which is why I have not provided it here. Here is the code that I have so far with comments explaining what I need in the code. Any help with this would be huge.

Thanks

Private Sub Workbook_Open() 'Saves filename as value of C10 Dim newFile As String, fName As String fName = Range("C10").Value newFile = fName & " " & Range("E9").Value ChDir Range("A8") ActiveWorkbook.SaveCopyAs Filename:=newFile 'Need code to delete a line of code or all code to prevent the code above from executing when the new Worksheet that has been created is opened. ActiveWorkbook.Close False End Sub

No unfortunately not as I will be adding code to the new copy as well. — Kam
– Kam, Commented Feb 15, 2018 at 16:24

Foxfire And Burns And Burns · Accepted Answer · 2018-02-15 16:52:30Z

Well, this code will delete the event. You save a copy of your workbook, so my idea is

you do whatever you do
Delete the Workbook_open Macro
Save to a new workbook (that part of macro won't be)
DO NOT SAVE CHANGES IN YOUR ORIGINAL WORKBOOK, so the Workbook_Open macro will be always secure.

Anyways, test it first several times to make sure it works. Altering VBA code with VBA itself can produce weird things somethimes.

Hope this helps.

All credits go to OZGRIZ

Also, make sure you check in Excel Options->Trusted Sites->Settings of Trusted Sites-> Macros Setting-> Check Trust access to Visual Basic Editor

Sub DeleteWorkbookEventCode() ''Needs Reference Set To _ '"Microsoft Visual Basic For Applications Extensibility" 'Tools>References. 'Also, make sure you check in Excel Options->Trusted Sites->Settings of Trusted Sites-> Macros Setting-> Check Trust access to Visual Basic Editor Dim i As Integer Dim MyStart, MyEnd As Integer With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule MyStart = 0 For i = 1 To .CountOfLines Step 1 If .Lines(i, 1) = "Private Sub Workbook_Open()" Then MyStart = i If .Lines(i, 1) = "End Sub" And MyStart > 0 Then MyEnd = i Exit For End If Next i .DeleteLines MyStart, (MyEnd - MyStart) + 1 End With End Sub

It works perfectly. For future reference If wanted to use this on another module (For example, module 1) instead of typing "ThisWorkbook" next to VBComponents would I just type "Module1"?
First of all, if the answer worked for you, please, mark it as correct so the question will appear as closed. Second, about your question, take a look at Deleting Modules via VBA Code.

Collectives™ on Stack Overflow

Using code to delete code

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related