The_skinner
New Member
- Joined
- Jun 18, 2008
- Messages
- 15
Hello there,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><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
>
Thank-you for your time<o
></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


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


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


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


Rich (BB code):
Private Sub CommandButton1_Click()
Rich (BB code):
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

