Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
can I use code to make code in another book
I want to write a macro that will make a new workbook and I want the new
workbook to have a macro in it. Ideally, there will be a button on one of the sheets that will run it too. is that possible? Can I write a macro that will write a macro? Thanks M |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
can I use code to make code in another book
yes, but the way i normally use is to export the code module from the
existing workbook and import it into the new one. as an example, this would export the shade_rows module, the the path i have set in the fpath variable: ThisWorkbook.VBProject.VBComponents("Mod_Shade_Row s").Export Filename:=fPath & "Mod_Shade_Rows.bas" then, after creating the new workbook, i import that module: ActiveWorkbook.VBProject.VBComponents.Import Filename:=fPath & "Mod_Shade_Rows.bas" once in a while, i will create a code module, too. this adds a workbook_open and workbook_beforeclose module, watch out for wrapping by the forum/news reader. the long lines that wrap are all on one line: AddProcedureToModule() Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("Thisworkbook") Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CountOfLines + 1 .InsertLines LineNum, "Private Sub Workbook_Open()" LineNum = LineNum + 1 .InsertLines LineNum, "UserForm3.Show" LineNum = LineNum + 1 .InsertLines LineNum, "Application.MacroOptions Macro:=" & """Show_UserForm3""" & ", Description:=""" & """" & ", ShortcutKey:=" & """F""" LineNum = LineNum + 1 .InsertLines LineNum, "Application.MacroOptions Macro:=" & """Shade_Rows""" & ", Description:=""" & """" & ", ShortcutKey:=" & """P""" LineNum = LineNum + 1 .InsertLines LineNum, "CreateMenubar3" LineNum = LineNum + 1 .InsertLines LineNum, "End Sub" LineNum = LineNum + 1 .InsertLines LineNum, "Private Sub Workbook_BeforeClose(Cancel As Boolean)" LineNum = LineNum + 1 .InsertLines LineNum, "RemoveMenubar3" LineNum = LineNum + 1 .InsertLines LineNum, "Application.MacroOptions Macro:=" & """Show_UserForm3""" & ", Description:=""" & """" & ", ShortcutKey:=" & """""" LineNum = LineNum + 1 .InsertLines LineNum, "Application.MacroOptions Macro:=" & """Shade_Rows""" & ", Description:=""" & """" & ", ShortcutKey:=" & """""" LineNum = LineNum + 1 .InsertLines LineNum, "End Sub" End With -- Gary Keramidas Excel 2003 "Michelle" wrote in message ... I want to write a macro that will make a new workbook and I want the new workbook to have a macro in it. Ideally, there will be a button on one of the sheets that will run it too. is that possible? Can I write a macro that will write a macro? Thanks M |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
can I use code to make code in another book
How about some alternatives?
If you're creating multiple workbooks that need the same macro, then I wouldn't want to put copies of the same macro in all those workbooks. When (not if!), they need to be updated, you'll never be able to find all the files that were created (and then used to create more!). Instead, I'd create a single addin file. This would have the macro in it and a way to run that macro (menubar, toolbar, QAT, ribbon modifications). When I do this kind of thing, the code can usually run against the activesheet in any workbook. You may want to add a "are you sure" prompt -- or even check some indicator on that sheet (a hidden name on that sheet???) before continuing. For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003 and xl2007. http://msmvps.com/blogs/xldynamic/ar...me-simple.aspx =================== Alternative #2. If you have to have code in the new workbooks, then I wouldn't create the code on the fly. There's user security setting that will stop your code from running if the user chooses not to allow this kind of code. Instead you could create a workbook template (*.xlt or *.xltm) that contains all the code you need. And you could also include any other common details that you need -- page layout on each sheet, filters, event macros, ... Then use that template file when you're creating the new workbook. Option Explicit Sub testme() Dim TemplFileName As String Dim NewWkbk As Workbook TemplFileName = "C:\path to template\template.xlt" Set NewWkbk = Workbooks.Add(template:=TemplFileName) End Sub You could even protect the template's project (in the VBE) and that may help keep prying eyes from making (unauthorized) changes to the code. Michelle wrote: I want to write a macro that will make a new workbook and I want the new workbook to have a macro in it. Ideally, there will be a button on one of the sheets that will run it too. is that possible? Can I write a macro that will write a macro? Thanks M -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
can I use code to make code in another book
And if you save your workbook with save as and get to it a new name? It's
more easy to do. Regards, Ste' "Michelle" wrote: I want to write a macro that will make a new workbook and I want the new workbook to have a macro in it. Ideally, there will be a button on one of the sheets that will run it too. is that possible? Can I write a macro that will write a macro? Thanks M |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to protect all the worksheets in a book | New Users to Excel | |||
moved code to new book - some doesn't work now | Excel Programming | |||
Verify Canadian Postal Code ~ make the code work | Excel Programming | |||
'BeforeClose' code problems:book won't close if more than one book is open | Excel Programming | |||
How to make a button VBA code reference other VBA code subroutines??? | Excel Programming |