VBA extensibility Library
 

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.

VBA extensibility Library
 

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"aspadda" wrote in message om...
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.

VBA extensibility Library
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"aspadda" wrote in message om...
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.

VBA extensibility Library
 

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?

Ron de Bruin wrote:

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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Ron de Bruin" wrote in message ...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"aspadda" wrote in message om...
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.



--

Dave Peterson