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?

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?

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

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
.