Daxton's Question: New Worksheet Button on Every Worksheet

[Anne Troy]

Sub newWorksheet()
' newWorksheet Macro
' Macro recorded 6/9/2004 by VMM
Dim latestSheet As Integer
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Worksheets.Count).Select
Sheets.Add
Sheets(Worksheets.Count).Select
Sheets("Sheet6").Move After:=Sheets(14)
Sheets(Worksheets.Count).Select
ActiveSheet.Shapes("Button 1").Select
Selection.Copy
Range("A1").Select
Sheets(Worksheets.Count - 1).Select
Range("A1").Select
ActiveSheet.Paste
Selection.ShapeRange.IncrementLeft 3.75
Range("A1").Select
latestSheet = Worksheets.Count - 1
Sheets(latestSheet).Move After:=Sheets(Sheets.Count)
End Sub

[Daxton A.]

Does anyone know how to get this code to work. Its not that important to me but I just want to know. Cuz what I want to do is Have a button on ever worksheet that I create in this one workbook that creates a new sheet everytime it is pressed and from any page. But the code right there only works if it is pressed on the last sheet.:dunno

And Thank You again Dreamboat!

[Zack Barresse]

Hi Daxton,

If you want to just add a new sheet from anywhere in the workbook, and place that new sheet at the end of all the others, this'll do ya:


Sub addSheet()
Sheets.Add
ActiveSheet.Move after:=Sheets(Worksheets.Count)
End Sub

Put it in a regular module. Assign a shortcut key if you'd like, or assign to buttons on every sheet. My preference would be a shortcut key. Are you going to want to prompt for a new name also? That'd be easy enough to add too.

[NateO]

Hello try the following:


Private Sub Ad_SheetNButton()
Dim ws As Worksheet, cb As OLEObject
Application.ScreenUpdating = False
With ThisWorkbook
.Worksheets.Add , .Sheets(.Sheets.Count)
Set ws = .Sheets(.Sheets.Count)
End With
With ws
Set cb = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=.Cells(1, 1).Left, _
Top:=.Cells(1, 1).Top, _
Width:=.Cells(1, 1).Width * 2, _
Height:=Cells(1, 1).RowHeight * 2)
With cb
.Placement = xlMove
With .Object
.BackColor = &HFF&
.Caption = "Press Me"
End With
End With
ThisWorkbook.VBProject.VBComponents(.CodeName).CodeModule. _
insertlines 1, _
"Private Sub CommandButton1_Click()" & vbLf & _
"Application.Run ""Ad_SheetNButton""" & vbLf & _
"End Sub"
End With
Application.ScreenUpdating = True
End Sub

[Steiner]

Hi Nate,


your code is quite cool:cool , but how about modifiying the line that inserts the code, because your version has a problem with option explicit turned on: your code is inserted before it, and I always get an error pressing the new button. But with the following modification the code will be added to the end of the code that is already there:



ThisWorkbook.VBProject.VBComponents(.CodeName).CodeModule. _
insertlines ThisWorkbook.VBProject.VBComponents(ThisWorkbook.CodeName).CodeModule.Count OfLines + 1, _
"Private Sub CommandButton1_Click()" & vbLf & _
"Application.Run ""Ad_SheetNButton""" & vbLf & _
"End Sub"

[Daxton A.]

:vv Just want to say thank you 2 everyone for good responses.

[NateO]

Works for me Steiner. While I declare all variables, I dont use option explicit; the thought had not occurred to me.