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

KevH

Board Regular
Joined
Apr 24, 2007
Messages
104
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
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:

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
 
Upvote 0

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
Kev

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

KevH

Board Regular
Joined
Apr 24, 2007
Messages
104
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.
 
Upvote 0

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
Kev

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

KevH

Board Regular
Joined
Apr 24, 2007
Messages
104
Hm...

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
Well you don't save worksheets.:)

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

KevH

Board Regular
Joined
Apr 24, 2007
Messages
104
Oooo... I can just make a generic sheet, hide it, and then copy paste it.

Weeeeee....


:p
 
Upvote 0

KevH

Board Regular
Joined
Apr 24, 2007
Messages
104
Ok so in my "main sheet" I have

Code:
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?
 
Upvote 0

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
Kev

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

You actually copy the sheet.
Code:
With Sheets("TheTemplate")
    .Visible = xlSheetVisible
    .Copy After:=Worksheets(Worksheets.Count)
    Set wsNew = ActiveSheet
    wsNew.Name = "New Template"
    .Visible = xlSheetHidden
End With
 
Upvote 0

Forum statistics

Threads
1,191,092
Messages
5,984,604
Members
439,896
Latest member
SquareCare

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top