M
Monk
Hello
The following code deletes VBA in the file for users however I get a runtime
error 13 on the following line for those that are still using Excel 97. Is
there code that will work for Excel 97 and later versions or is this a
security setting on 97 which I cannot locate.
Error Line
Set VBComps = ActiveWorkbook.VBProject.VBComponents
Full Code
Dim oSht As Object
Application.DisplayAlerts = False 'suppress delete warning
For Each oSht In Sheets
If Not oSht.Visible Then oSht.Delete
Next
Application.DisplayAlerts = True
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
The following code deletes VBA in the file for users however I get a runtime
error 13 on the following line for those that are still using Excel 97. Is
there code that will work for Excel 97 and later versions or is this a
security setting on 97 which I cannot locate.
Error Line
Set VBComps = ActiveWorkbook.VBProject.VBComponents
Full Code
Dim oSht As Object
Application.DisplayAlerts = False 'suppress delete warning
For Each oSht In Sheets
If Not oSht.Visible Then oSht.Delete
Next
Application.DisplayAlerts = True
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