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]

Rory. Are you going to persue an example using a Toolbar? That is the way I would go with this. I don't want to dupicate your efforts...

Skinner. "common routine"

Rory is simply stating that the code would be moved to a location that is accessible by all worksheets such as a standard module or as a public procedure in a public object module. I am assuming that he will walk you through this. If not, I'll be back.

 

[RoryA]

Here's an example. Insert a normal module in your workbook (Insert->Module in the VBEditor) and paste this in, changing the macro name and button caption as you like:

Public Const gcstrAPP_NAME As String = "MyApp"
 
Sub SetUpCbars()
   Dim ctl As CommandBarControl
   Dim strWBName As String
   strWBName = "'" & ThisWorkbook.Name & "'!"
   On Error Resume Next
   Set cbr = Application.CommandBars(gcstrAPP_NAME)
   If Not cbr Is Nothing Then cbr.Delete
   Set cbr = Application.CommandBars.Add(Name:=gcstrAPP_NAME, Position:=msoBarFloating, MenuBar:=False, temporary:=True)
   Set ctl = cbr.Controls.Add(Type:=msoControlButton)
   With ctl
      .Caption = "Your menu caption here"
      .OnAction = strWBName & "MacroNameHere"
      .Style = msoButtonCaption
   End With
   cbr.Visible = True
End Sub
Sub CleanUpCBars()
    On Error Resume Next
    Application.CommandBars(gcstrAPP_NAME).Delete
End Sub
Sub HideBar()
   On Error Resume Next
   Application.CommandBars(gcstrAPP_NAME).Visible = False
End Sub
Sub ShowBar()
   On Error Resume Next
   Application.CommandBars(gcstrAPP_NAME).Visible = True
End Sub

Then in the ThisWorkbook module (locate the ThisWorkbook item for your project in the Project Explorer - top left window usually - and double-click it) add this code:

Private Sub Workbook_Activate()
   ShowBar
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    CleanUpCBars
End Sub
Private Sub Workbook_Deactivate()
   HideBar
End Sub
Private Sub Workbook_Open()
    SetUpCbars
End Sub

Then all you need to do is ensure that your macro is in a normal module within the workbook and that all sheet references refer to the activesheet.

HTH

 

[The_skinner]

Oh bugger,

I think you guys are right about the security issues...

After a bit of swearing and soul searching i figured out a process for doing this that doesn't involve dynamically executing code on their machines.

Excel really is limited as a organisational tool because of this trait. But then again it's a spreadsheet program, not a database tool...

Thanks for all your input, if there is any particular charity you support let me know, and your email address for the receipt.

 

[The_skinner]

Ah i didn't realise that you'd written that code. Does that get round the security issue?

 

[RoryA]

It should do as you don't have to add code or buttons to the workbook dynamically. Your macro simply works with whatever sheet is active, and the macro is called by the toolbar button which floats above the workbook, no matter what sheet is active.

 

[The_skinner]

That's awesome! Hats off to you.

Is it possible to prevent this tool bar from being closed?

 

[Legacy 98055]

Yes. Using a commandbar would not raise any issues. Under medium security, the user would simply be prompted to enable or disable macros...

 

[RoryA]

Yes - add:

   cbr.Protection = msoBarNoChangeVisible

to the SetUpCBars routine.

 

[The_skinner]

Rorya,

Your cbars code works brilliantly, thank-you so much for that. One further question: is it possible to set the position of the tool bar? I've tried putting left and top in but it didn't like it

 

[RoryA]

Where do you want it?