Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Executing macro in one book from another.
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Executing macro in one book from another.
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 --- Regards, Norman |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Executing macro in one book from another.
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. Norman Jones wrote: 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 --- Regards, Norman |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Executing macro in one book from another.
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 --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Highlight Range - wrong macro, please edit. | Excel Worksheet Functions | |||
Need Macro for List of Book Titles | Excel Worksheet Functions | |||
Executing macro for all worksheet from a different worksheet | New Users to Excel |