Thanks.
The full code I have is attached below where there is a save as before the
delete VB part. Is there still a problem?
Security is (and was) enabled.
Thanks for your help
Sub copyclean()
'cleans formulas
For Each ws In Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
'savesAS
ActiveWorkbook.SaveAs Filename:="200905011_B", FileFormat:=xlNormal,
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False
DeleteAllVBA
End Sub
Sub DeleteAllVBA()
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Set VBComps = ActiveWorkbook.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
..DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub
"Joel" wrote:
> 1) The activeworkbook cannot be the workbook where the macro is running. The
> code cant delete itself.
> 2) Check you security seeting on the worksheet. Enable the option Trust
> Access to the VBA Project Object model.
>
> in 2003 it is on the menu Tools - Macro - Security Setting
>
> in 2007 Excel OPtions - Trust Center - Trust Center stting
>
> "LiAD" wrote:
>
> > Hi,
> >
> > I have this code to try to delete whatever code is inside a file but it
> > doesn't seem to work. Would some-one be able to tell me why please or what
> > code I should use to delete whatever VB is in a file?
> >
> > Thanks
> > LiAD
> >
> >
> > Sub DeleteAllVBA()
> >
> > Dim VBComp As VBIDE.VBComponent
> > Dim VBComps As VBIDE.VBComponents
> >
> > Set VBComps = ActiveWorkbook.VBProject.VBComponents
> >
> > For Each VBComp In VBComps
> > Select Case VBComp.Type
> > Case vbext_ct_StdModule, vbext_ct_MSForm, _
> > vbext_ct_ClassModule
> > VBComps.Remove VBComp
> > Case Else
> > With VBComp.CodeModule
> > .DeleteLines 1, .CountOfLines
> > End With
> > End Select
> > Next VBComp
> > End Sub
> >
|
Similar Threads
