Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
write vba to a worksheet via a macro
i have a macro that deletes tabs a, b, c, and then re-creates them with the
current information i enter into tab d. i have a worksheet (beforedoubleclick) procedure that i want to incorporate into worksheet b...but the procedure is deleted each time the macro is run because the deletion of worksheet b is part of the macro. is there a way for the macro to write the beforedoubleclick procedure to worksheet b after it has been deleted and re-added? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
write vba to a worksheet via a macro
Hi
You probably need to use the SheetBeforeDoubleClick within the ThisWorkbook module. This gives you the option of passing it the sheet where the doubleclick applies. Something like: Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) If Sh.Name = "b" then 'your current code endif End Sub regards Paul On Jan 4, 9:06*pm, joemeshuggah wrote: i have a macro that deletes tabs a, b, c, and then re-creates them with the current information i enter into tab d. * i have a worksheet (beforedoubleclick) procedure that i want to incorporate into worksheet b...but the procedure is deleted each time the macro is run because the deletion of worksheet b is part of the macro. is there a way for the macro to write the beforedoubleclick procedure to worksheet b after it has been deleted and re-added? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
write vba to a worksheet via a macro
You can have code that writes code, but there are easier ways...
Instead of deleting the worksheet, just clear (or clearcontents) the cells. with worksheets("A") .cells.clearcontents 'remove values and formulas 'or .cells.clear 'remove all the formatting, too. 'or even limit it to a range .rows("3:99").clear 'clearcontents 'if you wanted to keep headers/footers/filters/... End with Or you could create a template sheet (hide it) and use that as the basis for the new sheet. That template would have everything you need--including the event procedures. Dim NewSheet as worksheet with worksheets("a-sheet-template") .visible = xlsheetvisible .copy _ after:=sheets(sheets.count) set NewSheet = activesheet 'new sheet based on template .visible = xlsheethidden end with 'and do stuff to the NewSheet Variable newsheet.name = "A" ==== But if you want, you can visit Chip Pearson's site: http://www.cpearson.com/excel/vbe.aspx joemeshuggah wrote: i have a macro that deletes tabs a, b, c, and then re-creates them with the current information i enter into tab d. i have a worksheet (beforedoubleclick) procedure that i want to incorporate into worksheet b...but the procedure is deleted each time the macro is run because the deletion of worksheet b is part of the macro. is there a way for the macro to write the beforedoubleclick procedure to worksheet b after it has been deleted and re-added? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
write vba to a worksheet via a macro
thanks!!!
"Dave Peterson" wrote: You can have code that writes code, but there are easier ways... Instead of deleting the worksheet, just clear (or clearcontents) the cells. with worksheets("A") .cells.clearcontents 'remove values and formulas 'or .cells.clear 'remove all the formatting, too. 'or even limit it to a range .rows("3:99").clear 'clearcontents 'if you wanted to keep headers/footers/filters/... End with Or you could create a template sheet (hide it) and use that as the basis for the new sheet. That template would have everything you need--including the event procedures. Dim NewSheet as worksheet with worksheets("a-sheet-template") .visible = xlsheetvisible .copy _ after:=sheets(sheets.count) set NewSheet = activesheet 'new sheet based on template .visible = xlsheethidden end with 'and do stuff to the NewSheet Variable newsheet.name = "A" ==== But if you want, you can visit Chip Pearson's site: http://www.cpearson.com/excel/vbe.aspx joemeshuggah wrote: i have a macro that deletes tabs a, b, c, and then re-creates them with the current information i enter into tab d. i have a worksheet (beforedoubleclick) procedure that i want to incorporate into worksheet b...but the procedure is deleted each time the macro is run because the deletion of worksheet b is part of the macro. is there a way for the macro to write the beforedoubleclick procedure to worksheet b after it has been deleted and re-added? -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How write Macro to save worksheet to a RELATIVE address | Excel Discussion (Misc queries) | |||
Can I record or write a macro to send a worksheet via Outlook? | Excel Discussion (Misc queries) | |||
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions | |||
Write to One Worksheet Only ? | Excel Programming | |||
IF then write new worksheet/book | Excel Programming |