VBA extensibility Library

[aspadda]

Chip Pearson examples/code from
http://www.cpearson.com/excel/vbe.htm
was very helpful in deleting the forms, modules etc before closing the
workbook.

Now i need to distribute this template to the users...but it won't
work
unless
1) VBA extensibility library is referenced
2) In security settings I check the "Trust access to Visual Basic
Project"

Is there a way out for this situation....as i need to distribute it to
unknown users.

Suggestions will be highly appreciated.

 

[Ron de Bruin]

Hi

For numer one you can use Late Binding

For example this macro from Chip's site
http://www.cpearson.com/excel/vbe.htm

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

You can change to this

Public Sub DeleteAllVBA()
Dim VBComp As Object
Dim VBComps As Object
Set VBComps = ActiveWorkbook.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case 1, 3, _
2
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub

Number two : I don't know

 

[Ron de Bruin]

I forgot to sat this

For number two you can build in a error check like this
http://www.j-walk.com/ss/excel/tips/tip96.htm

But as far as I know you can't change this setting with code

 

[Dave Peterson]

And just to add to Ron's response. This setting is a user setting. I wouldn't
want your code changing my settings.

If I had to have my group run this code, I think I'd give them the warning
message that Ron suggested and instructions on how to toggle it.

======
Any chance you could provide a separate addin that contains all the
code/userform so that you wouldn't have to eliminate the code from the real
workbook at all?