PDA

View Full Version : Completely remove form from Excel file



ErikNewbie
07-08-2007, 05:09 PM
I need to completely free an Excel file from all VB-code & objects. The code I found and reworked works fine, but I want to delete the form where a button initiates the code (it starts with myForm.Hide).

Anyone an idea?

The code I use:

Dim mSheet As Object, shtName As String
ActiveSheet.Shapes("CommandButton1").Delete
For Each mSheet In Sheets
shtName = mSheet.CodeName
With ThisWorkbook.VBProject.VBComponents(shtName).CodeModule
.DeleteLines 1, .CountOfLines
End With
Next
With ThisWorkbook.VBProject.VBComponents("myForm").CodeModule
.DeleteLines 1, .CountOfLines
End With

johnske
07-08-2007, 06:02 PM
You mean remove all VBA code and all objects? use...
Option Explicit
'
Sub DeleteAllObjects()
'
Dim Sheet As Worksheet
'
For Each Sheet In ThisWorkbook.Sheets
With Sheet
.Activate
.Shapes.SelectAll
Selection.Cut
End With
Next
'
Call AddReference
'
End Sub
'
Private Sub AddReference()
Dim Reference As Object
With ThisWorkbook.VBProject
For Each Reference In .References
If Reference.Description Like "Microsoft Visual Basic for Applications Extensibility*" Then GoSub Finish
Next
.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 0, 0
End With
'
Finish:
Call DeleteAllVBAcode
'
End Sub
'
Private Sub DeleteAllVBAcode()
'
Dim Component As VBIDE.VBComponent
'
For Each Component In ThisWorkbook.VBProject.VBComponents
'
Select Case Component.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
ThisWorkbook.VBProject.VBComponents.Remove Component
Case Else
With Component.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next
'
Call RemoveReference
'
End Sub
'
Private Sub RemoveReference()
'
Dim Reference As Object
'
For Each Reference In ThisWorkbook.VBProject.References
If Reference.Description Like "Microsoft Visual Basic for Applications Extensibility*" Then
ThisWorkbook.VBProject.References.Remove Reference
End If
Next
'
End Sub