Results 1 to 9 of 9

Thread: Delete Button

  1. #1
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Delete Button

    Hi,

    Codes in Workbook when opened this calls the checkShape macro.

    Code:
    Private Sub Workbook_Open()
        Application.ScreenUpdating = False
        With Worksheets("Sheet1")
            .Activate
        End With
        Application.ScreenUpdating = True
        Call checkShape
    End Sub
    checkShape Macro called from Workbook creates the Button on sheet.
    Code:
    Sub checkShape()
        With ActiveSheet.Buttons.Add(143, 10, 143, 40)
            .Name = "Button 1"
            .OnAction = "Macro1"
            .Caption = "Import File"
            With .Font
                .Name = "Times New Roman"
                .Size = 18
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
            End With
        End With
    End Sub
    How to delete this from sheet and create a new one on its place.

    Code:
    Macro1
    do something...
    ...
    call Macro2
    do something...
    ......
    
    delete the checkShape Macro.
    
    End Sub
    At the end of Macro2 delete checkShape and create new button for print option.

    Code:
    Sub CreateButton2()
        With ActiveSheet.Buttons.Add(143, 10, 143, 40)
            .Name = "Button 2"
            .OnAction = "PrintMenu"
            .Caption = "Print Menu"
            With .Font
                .Name = "Times New Roman"
                .Size = 18
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
            End With
        End With
    End Sub
    Delete checkShape on exit of Macro2 and replace with new.
    Thanks.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Here is some code from Chip Pearson's web site that will delete the macro.

    Code:
    Sub DeleteProcedureFromModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim StartLine As Long
        Dim NumLines As Long
        Dim ProcName As String
            
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        Set CodeMod = VBComp.CodeModule
    '------------------------------------------------------    
        'Macro1
            'do something...
    '------------------------------------------------------ 
        'call Macro2
            'do something...
    '------------------------------------------------------         
        ProcName = "checkShape"
        With CodeMod
            StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
            NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
            .DeleteLines StartLine:=StartLine, Count:=NumLines
        End With
    End Sub
    You must set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3

    HTH,
    Maud
    Last edited by Maudibe; 2016-01-28 at 21:27.

  3. #3
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks Maudibe for providing solution, is it possible to only temporarily delete or remove the macro and then reinstate when reopened.

    Thanks.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi zmagic

    see attached file for another approach.

    By giving names to the button shapes (right-click shape to select, then type a name in the name box to the left of the formula bar etc etc), you can easily control the hiding or display of buttons.
    This might be easier than creating buttons and deleting button etc etc.

    The attached file simply 'shows' the require import button on open.
    The pre-assigned routine attached to the import button will then 'hide' this button and then 'unhide' the print menu button etc etc etc.

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2016-01-30)

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Z,

    If Zeddy's approach is what you are looking for, then instead of making one visible and hiding the other, why not just have one button then just change the caption and assigned macro? You already wrote the code to do it:

    Code:
    Sub ButtonController()
    With ActiveSheet.Buttons("Button 1")
        .Caption = "Print Menu"
        .OnAction = "PrintMenu"
    End With
    End Sub
    Maud

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    good point!
    ..but if you have lots of 'button functions', that method can get cumbersome to keep track.
    I have often had dozens of 'buttons' on a startup sheet, and used the login User ID to determine which buttons to make 'visible' and, for 'admin' Users, which 'extra' buttons to display.
    Just goes to show there are plenty ways of doing stuff in Excel!

    zeddy

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    True zeddy, that is one way. But keeping track of which ones are visible with all the different names used in the code and varying positions seems much more daunting to me then pairing a caption with the action to a single button. Guess no real right or wrong way; just coder preference I guess.

    Maud

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    I remember using single buttons and changing the caption text, as you suggest.
    I used 'blue buttons' and made the caption text 'white' (to indicate the button was 'ON') and caption text 'black' (to indicate the button was in an 'OFF' state.
    Those were the days.
    I still do that now and then.

    zeddy

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Yeah, I do it "off and on" as well.

  11. The Following User Says Thank You to Maudibe For This Useful Post:

    zmagic (2016-02-07)

Posting Permissions

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