Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Extensibility library and "late binding" | Excel Discussion (Misc queries) | |||
Using C++ Dll library | Excel Programming | |||
Reference Library - Missing Library in a lower version. | Excel Programming | |||
VBA Extensibility | Excel Programming | |||
VBA Extensibility problem | Excel Programming |