Geen macro's, toch boodschap

[AABE]

Geacht Forum,

ik kopieer een worksheet.

worksheet.copy

Met dit commenado wordt de VBA code van de worksheet ook meegenomen
Met de volgende code verwijder ik deze VBA code.

For Each vc In ActiveWorkbook.VBProject.VBComponents
     If vc.Type = 100 Then vc.CodeModule.DeleteLines 1, vc.CodeModule.CountOfLines
     If vc.Type <> 100 Then ActiveWorkbook.VBProject.VBComponents.Remove vc
Next

Als ik daarna de sheet weer open, dan krijg ik het scherm "Enable Macro's, terwijl er geen macro's aanwezig zijn. Is dit te vermijden?


mvg,

Aat

 

[paulva]

Misschien opslaan als xlsx als je in Office2007 of 2010 werkt? Die slaat nooit macro's op.

 

[AABE]

Ik werk nog met 2003

 

[OctaFish]

Ik gebruik (een variant van) de routine ook, en heb er geen problemen mee. Misschien staat je macro-beveiliging nog niet goed ingesteld.

 

[wiki]

Sub DeleteAllCode()
     
     'Trust Access To Visual Basics Project must be enabled.
     'From Excel: Tools | Macro | Security | Trusted Sources
     
    Dim x               As Integer
    Dim Proceed         As VbMsgBoxResult
    Dim Prompt          As String
    Dim Title           As String
     
   ' Prompt = "Are you certain that you want to delete all the VBA Code from " & _
    'ActiveWorkbook.Name & "?"
    'Title = "Verify Procedure"
     
    'Proceed = MsgBox(Prompt, vbYesNo + vbQuestion, Title)
    'If Proceed = vbNo Then
     '   MsgBox "Procedure Canceled", vbInformation, "Procedure Aborted"
      '  Exit Sub
    'End If
     
    On Error Resume Next
    With ActiveWorkbook.VBProject
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents.Remove .VBComponents(x)
        Next x
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents(x).CodeModule.DeleteLines _
            1, .VBComponents(x).CodeModule.CountOfLines
        Next x
    End With
    On Error GoTo 0
    
     On Error Resume Next
    ActiveSheet.DrawingObjects.Visible = True
    ActiveSheet.DrawingObjects.Delete
    On Error GoTo 0

     
End Sub

Krijg je met deze code ook nog een melding?

gr wim

 

[AABE]

Sub DeleteAllCode()
     
     'Trust Access To Visual Basics Project must be enabled.
     'From Excel: Tools | Macro | Security | Trusted Sources
     
    Dim x               As Integer
    Dim Proceed         As VbMsgBoxResult
    Dim Prompt          As String
    Dim Title           As String
     
   ' Prompt = "Are you certain that you want to delete all the VBA Code from " & _
    'ActiveWorkbook.Name & "?"
    'Title = "Verify Procedure"
     
    'Proceed = MsgBox(Prompt, vbYesNo + vbQuestion, Title)
    'If Proceed = vbNo Then
     '   MsgBox "Procedure Canceled", vbInformation, "Procedure Aborted"
      '  Exit Sub
    'End If
     
    On Error Resume Next
    With ActiveWorkbook.VBProject
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents.Remove .VBComponents(x)
        Next x
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents(x).CodeModule.DeleteLines _
            1, .VBComponents(x).CodeModule.CountOfLines
        Next x
    End With
    On Error GoTo 0
    
     On Error Resume Next
    ActiveSheet.DrawingObjects.Visible = True
    ActiveSheet.DrawingObjects.Delete
    On Error GoTo 0

     
End Sub

Krijg je met deze code ook nog een melding?

gr wim

 

[AABE]

Perfect.... bedankt Wim