Executing macro in one book from another.

M

michaelberrier

I'm attempting to adapt Chip Pearson's code for deleting all code in a
workbook to run from a different workbook.

I've tried adding "With Workbooks("Book1") before the code in Book2,
but that still deletes all the code in Book2. Here is the code that I
started with:

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
Thanks for the help.
 
N

Norman Jones

Hi Michael,
I'm attempting to adapt Chip Pearson's code for deleting all code in a
workbook to run from a different workbook.

I've tried adding "With Workbooks("Book1") before the code in Book2,
but that still deletes all the code in Book2. Here is the code that I
started with:

Try replacing
Set VBComps = ActiveWorkbook.VBProject.VBComponents

with

Set VBComps = Workbooks("Book2"). _
VBProject.VBComponents

or

Set VBComps = Workbooks("Book2.xls"). _
VBProject.VBComponents

if the workbook has been saved
 
M

michaelberrier

Norman,
That works great. Now, I'd like to make the book that code will be
delete from dynamic based on the value of a combobox in a userform.
I've tried some subsitituions in the code but can't make it work. Any
ideas?

thanks again.
 
N

Norman Jones

Hi Michael,
That works great. Now, I'd like to make the book that code will be
delete from dynamic based on the value of a combobox in a userform.
I've tried some subsitituions in the code but can't make it work. Any
ideas?

Try:

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Dim WB as workbook

Set WB= Workbooks( UserForm1.ComboBox1.Value & ".xls")

Set VBComps = WB.VBProject.VBComponents
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top