Sunday, March 13, 2016

How to auto delete macro code after execution

We can also auto delete macro code writen in excel workbook. Some times workbooks are generated thru the java/.net and other code, it can have macro codes or can have compatibility issues. To remove those issues we have to delete macro codes.

Lets edit macro code from previous post.

Sub Auto_Open()

    Dim wk As Worksheet
    ' Declare object variable
    Dim vbCom As Object
    Set vbCom = Application.VBE.ActiveVBProject.VBComponents
   
    MsgBox "Fill Values in cell of all sheets"
    For Each wk In ThisWorkbook.Worksheets
    wk.Activate
    With wk.Range("I5")
    .FormulaR1C1 = "testqqq"
    End With
    Next wk

vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
   
End Sub

 
* note - highlighed code in yellow color is newly added in previous post.

Now save the macro code close and reopen the workbook.

ERROR:
Run-time error '1004':

Programmatic access to Visual Basic Projects is not trusted


SOLUTION
Please look for below screen shot and follow the same process and enable the trust access to the VBA code

   

Close and reopen the workbook.. It works!!!!!!

No comments:

Post a Comment

web stats