PDA

View Full Version : Daxton's Question: New Worksheet Button on Every Worksheet



Anne Troy
06-10-2004, 01:39 PM
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.
06-10-2004, 01:45 PM
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
06-10-2004, 03:25 PM
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
06-10-2004, 05:19 PM
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
06-10-2004, 10:39 PM
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.
06-11-2004, 06:15 AM
:vv Just want to say thank you 2 everyone for good responses.

NateO
06-11-2004, 07:57 AM
Works for me Steiner. While I declare all variables, I dont use option explicit; the thought had not occurred to me.