write vba to a worksheet via a macro

[joemeshuggah]

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?

 

[paul.robinson]

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

 

[Dave Peterson]

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]

thanks!!!

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