Copying sheets - keeping control code

P

Pozzo

I have a template that has some controls on it with associated code.

It is necessary for my task to dynamically create worksheets based o
this template. My problem is that the names of the controls chang
when a sheet is copied and therefore when used Excel cannot find an
code to execute and the control does not perform its function. I coul
set up a workbook with, for example, 100 templates and delete 90 if th
user only needs 10, but this seems really messy.

Any suggestions gratefully received.

Thanks
 
P

Pozzo

Sorry, I hope nobody minds me bumping this, since I didn't manage to ge
an answer.

Maybe it would help if I give an outline of my problem. I need t
create a number of workbooks, each with a number of sheets, eac
individual one a sales forecast for a product. There are comman
buttons etc. on the template I have created for one of these sheets.
am getting out of my depth slightly, but the basic idea is that I wan
the user to be able to select the products he/she wants on th
workbook, and then somehow use this template to initialise all th
sheets. The biggest problem here is how to make sure that the comman
buttons function in the same way as they do on my template. Is thi
possible
 
D

Dave Peterson

If you use controls from the control toolbox toolbar, you can copy that sheet
and the control and the code behind the control will copy with it.

So if I had a worksheet named Master that had a bunch of controls from that
control toolbox toolbar and I needed to make 10 copies of it, I could do this:

For example, I put a commandbutton (from the control toolbox toolbar) on a
worksheet named Master.

I had this code under the sheet:

Option Explicit
Private Sub CommandButton1_Click()
MsgBox "hi from: " & Me.Parent.Name & "--" & Me.Name
End Sub


Then in a general module, I copied that sheet 10 times with this:

Option Explicit
Sub testme()

Dim mstrWks As Worksheet
Dim iCtr As Long

With ThisWorkbook
Set mstrWks = .Worksheets("master")
For iCtr = 1 To 10
mstrWks.Copy _
after:=.Worksheets(.Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = "MySheet" & iCtr
If Err.Number <> 0 Then
MsgBox "Please rename " & ActiveSheet.Name & " manually"
Err.Clear
End If
On Error GoTo 0
Next iCtr
End With

End Sub

This'll even work if you want to copy to a new workbook. Just copy it (with no
before/after parm):

Option Explicit
Sub testme()

Dim mstrWks As Worksheet
Dim iCtr As Long

With ThisWorkbook
Set mstrWks = .Worksheets("master")
For iCtr = 1 To 10
mstrWks.Copy
On Error GoTo 0
Next iCtr
End With

End Sub

There are other alternatives, too. You could separate your code that copies the
stuff into a different workbook. Then create a new workbook that would be used
for a template--it would contain the controls, code and even all the formatting
you wanted.

Then use that as the basis for the new worksheet/workbook.
 
Top