Can Worksheet.add do tab names, buttons, and VBA code?

[KevH]

Howdy all,

Thanks to the archive I was able to find a way to add worksheets and prepopulate some of the cells.

http://www.mrexcel.com/archive/VBA/6439.html

I was wondering if there is also a way to:

- set the name for the tab
- add a command button
- add the underlying VBA

I'm guessing the tab name is easy, but following what I can from the help files results in compile errors.

 

[Davers]

Well, I'll try to keep this brief. The below code will rename Sheet1 to "Hello", insert a command button, and add code to the command button that will lock the worksheet with a password of "password", without the quotes of course. In order for this code to work, you will need to set a reference to Microsoft Visual Basic for Applications Extensibility X.X (whatever your version number is). And if you get a weird error about "Programmatic access to the VBProject is not trusted"...that setting is under your "Tools" - "Options" - "Security" - "Macro Security" - "Trusted Sources" tab - check the box labeled "Trust access to Visual Basic Project". Here is the code:

Sub AddProcedure()

Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule

With Worksheets("Sheet1")
    .Name = "Hello"
    .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=78.75, Top:=33, Width:=72, Height:=24).Select
End With

Range("A1").Select

With VBCodeMod
    LineNum = .CountOfLines + 1
    .InsertLines LineNum, _
"Private Sub CommandButton1_Click()" & vbCrLf & _
"   Dim mySht As Variant" & vbCrLf & vbCrLf & _
"    For Each mySht In ActiveWorkbook.Sheets" & vbCrLf & _
"        mySht.Protect Password:=""password"", DrawingObjects:=True, _" & vbCrLf & _
"            Contents:=True, Scenarios:=True" & vbCrLf & _
"        mySht.EnableSelection = xlNoSelection" & vbCrLf & _
"    Next" & vbCrLf & vbCrLf & _
"End Sub"

End With

End Sub

Hope this helps!

Dave

 

[Norie]

Kev

Could you not just create a template worksheet and copy that?

 

[KevH]

Could you not just create a template worksheet and copy that?

Davers,

I think you'll agree with me on the following:

Norie = Rock Star

Now to go read up on templates.

 

[Norie]

Kev

Don't dismiss Dave's suggestion, that's the way to write code using code.

 

[KevH]

Hm...

I want to keep this as a single file if possible. Can you save a template inside your current workbook (and hide it)?

 

[Norie]

Well you don't save worksheets.

But you can easily hide them by going to Format>Sheet>Hide

 

[KevH]

Oooo... I can just make a generic sheet, hide it, and then copy paste it.

Weeeeee....

 

[KevH]

Ok so in my "main sheet" I have

Public Sub CommandButton1_Click()

    Sheets("TheTemplate").Select
    Selection.Copy
    Selection.Paste _
    Destination:= ?

End Sub

What do I put in the destination to have this sheet save as a new tab in this workbook?

 

[Norie]

Kev

Well for a start you don't use any of that selecting or pasting.

You actually copy the sheet.

With Sheets("TheTemplate")
    .Visible = xlSheetVisible
    .Copy After:=Worksheets(Worksheets.Count)
    Set wsNew = ActiveSheet
    wsNew.Name = "New Template"
    .Visible = xlSheetHidden
End With