Delete command button to be hidden at the first sheet and appear at the second sheet

  • Good day everybody.


    I'm now doing some basic coding for excel.
    Would like to find out how do you make a command button hidden on the first sheet and appear at the second sheet?
    For example, the first sheet will only have a "Additional Page" command button and when u press that, the second sheet will appear. But instead of only "Additional page" button on the second sheet, there will be "Delete this page" command button appearing as well.


    So in short, what code do i use to make it hidden at the first sheet but appear at the second sheet and so on?
    My current code is this:


    [VBA]Sub Additionalpagedelete()
    Application.Screenupdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    Activeworkbook.Unprotect = "1"
    Activesheet.Unprotect = "1"
    Activesheet.Delete
    Activeworkbook.protect "1", Structure:=True, Windows:=False
    'Activesheet.Protect "1", Drawingobjects:=True, Contents:=True, Scenarios: _
    True
    End Sub[/VBA]


    This code works fine.

  • I am presuming the additional page is just a copy of the original page with the "additional page" button


    If so, just ensure you keep the button hidden at all times on the original page... you can set this with code, or at design time by accessing its properties

    Code
    1. ActiveSheet.Shapes("CommandButton1").Visible = False




    Where you have the code to copy / create the additional page, AFTER the page is copied (because after you copy the sheet everything is copied and the copied sheet becomes the active sheet):


    Code
    1. ActiveSheet.Shapes("CommandButton1").Visible = True


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________



  • Thank you Ger! I will try it and update again!

  • So to make things clearer,
    Example, sheet1 is my original page which only contains "Additional page" Command Button.
    Which i wanted to create "Delete page" command button as well.
    But that will only appear in sheet2, sheet3 and so on when i press the "additional page" command button.


    So its like I didn't want "Delete page" to appear in my original sheet which is sheet1.
    But I want to appear in sheet2, sheet3 and so on.


    This is my "Additional Page" Button code:


    [VBA]Sub AdditionalPage()
    Dim sht As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next

    ActiveSheet.Copy after:=ActiveSheet
    ActiveSheet.Shapes("Delete page").Visible = True
    End Sub


    [/VBA]


    And this is my "Delete Page" Button code:


    [VBA]Sub additionalpagedelete()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next

    ActiveSheet.Delete
    Activesheet.Shapes("Delete Page").Visible = False
    End Sub[/VBA]


    Kindly advise me on how to do it because I did what Ger told me and it didn't work. The "Delete page" button disappear on both sheet2 and sheet3.

  • Just to be clear - you need to understand the logic flow here...


    1. Your original sheet must have the Additional Sheet Button AND a delete button
    2. Hide the Delete button by changing its .visible property to false in design mode so that no one ever sees the delete button the original page.
    3. When the additional page button is pressed, create a copy of the page (the active sheet at that point in time). This copies the sheet, its contents its TWO buttons (hidden and unhidden) and code associated with the buttons
    4. The copied sheet becomes active, so now HIDE the additional button sheet (becuase you dont want an additional page button on each additional page - presumably?), and UNHIDE the delete button.
    5. When someone presses the delete button on an additional page, just delete the page (this will delete the buttons too).


    Code below... and attached also.

    Files

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Why copy an existing sheet that presumably has data in it? I would think that data needs removing.


    I would have a hidden template sheet that can be copied similar to this invoice example.


    You can actually avoid having to delete buttons by adding a master sheet with the button on or maybe even add a button to the Ribbon


  • Thank you Ger! This is what I am looking for! Now it all makes sense. Thank you so much!

  • Why copy an existing sheet that presumably has data in it? I would think that data needs removing.


    I would have a hidden template sheet that can be copied similar to this invoice example.


    You can actually avoid having to delete buttons by adding a master sheet with the button on or maybe even add a button to the Ribbon


    Thank you Roy. But I have already found an answer! :)