Create New Worksheet Containing Buttons, not by template

[The_skinner]

Hello there,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I'm trying to create a macro that creates new worksheets with buttons on them that have macros attached. I've been able to produce one worksheet with a button and macro, but when the function that copies the code is looped it crashes out of Excel.<o></o>
<o></o>
I realise that the usual method of doing this would be by templates, but this code will be run on other people's computers so the template wouldn’t exist in the location that i had specified (I think with my limited experience with VBA - Please correct me if I'm wrong). I've also tried to copy the page with the button on, but this takes too long (even with screenupdating = false) as there are many sheets to be created.<o></o>
<o></o>
This is a stripped down version of the code that I have created for the purpose of this forum. To recap, the function that creates a new page, with button, with macro attached works. It's when it's looped that it crashes out. I've stepped through the program, and it reaches the second message box, then it gives up on life. <o></o>
<o></o>
I realise that financial incentives might be against this forum's code of conduct, but i will make an online donation to a charity of your choice if you can help me fix this code as it is making me hate life. <o></o>
<o></o>

Private Sub CommandButton1_Click()

Dim testvar As Boolean
Dim i As Integer
Dim continue As Boolean
continue = True
i = 1
Do Until continue = False 'loop until there's nothing in column A
  MsgBox "loop restarted"
 
  Sheets("Data").Select
  Worksheets.Add().Name = (i)
  testvar = copyheader(i)
  MsgBox "yeah!"
 
  If i >= 10 Then
      Exit Do
  End If
  i = i + 1
Loop
End Sub
Function copyheader(i As Integer) As Boolean
 
 
  Dim Name As String
  Dim NName As String
  Dim myCmdObj As OLEObject, N%
 
  Sheets(i).Select
 
  ' Set the name for the button
  NName = "cmdAction0"
 
   ' Add button
  Set myCmdObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
  Link:=False, DisplayAsIcon:=False, Left:=146.25, Top:=1.5, _
  Width:=570, Height:=22.5)
 
   ' Define buttons name
  myCmdObj.Name = NName
 
   ' Define buttons caption
  myCmdObj.Object.Caption = "Click for action"
 
   ' Inserts code for the button
  With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
      N = .CountOfLines
      .InsertLines N + 1, "Private Sub cmdAction0_Click()"
      .InsertLines N + 2, "End Sub"
  End With
 
End Function

Thank-you for your time<o></o>
<o></o>

 

[Legacy 98055]

 

[The_skinner]

That's got to have an impact on your fuel economy! You should bill her accordingly, then maybe she'd start to think about her behaviour. I've donated £5 to the NSPCC, but maybe Friends of the Earth would have been a better call, to deal with the environmental damage caused by your partner's carbon foot print.

Anyway, I should probably do some work now. I get to show this program off to my boss tonight who's going to be blown away with what i've (you've to a large extent) accomplished in a small space of time.

Thanks for all your help!

 

[RoryA]

We've just switched from a 3.2l V6 car to a 2.0l diesel so we're trying to reduce the footprint!