Consulting

Results 1 to 7 of 7

Thread: Daxton's Question: New Worksheet Button on Every Worksheet

  1. #1
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location

    Daxton's Question: New Worksheet Button on Every Worksheet

    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
    Last edited by Aussiebear; 04-30-2023 at 12:50 AM.
    ~Anne Troy

  2. #2
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    143
    Location

    Question:

    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.

    And Thank You again Dreamboat!
    Last edited by Daxton A.; 06-10-2004 at 01:58 PM.

  3. #3
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    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.

  4. #4
    VBAX Regular NateO's Avatar
    Joined
    Jun 2004
    Location
    Minneapolis, MN
    Posts
    90
    Location
    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
    Regards,
    Nate Oliver

  5. #5
    VBAX Tutor
    Joined
    May 2004
    Location
    Germany, Dresden
    Posts
    217
    Location
    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.CountOfLines + 1, _
    "Private Sub CommandButton1_Click()" & vbLf & _
    "Application.Run ""Ad_SheetNButton""" & vbLf & _
    "End Sub"

  6. #6
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    143
    Location

    Talking ;)

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

  7. #7
    VBAX Regular NateO's Avatar
    Joined
    Jun 2004
    Location
    Minneapolis, MN
    Posts
    90
    Location
    Works for me Steiner. While I declare all variables, I dont use option explicit; the thought had not occurred to me.
    Regards,
    Nate Oliver

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •