Hide / Unhide Buttons with the help of a code

  • Try this approach with no additional macros needed.

    Place this code at the end of your GenerateXML macro:

    Code
    With ActiveSheet
        .Shapes.Range(Array("Button 1")).Visible = False
        .Shapes.Range(Array("Button 2")).Visible = True
    End With

    Place this code at the end of your ClearData macro:

    Code
    With ActiveSheet
        .Shapes.Range(Array("Button 1")).Visible = True
        .Shapes.Range(Array("Button 2")).Visible = False
    End With

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Please attach a copy of your actual file including all your macros.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I will share it sometime. Please don't laugh at my codes and presentation. I am a beginner and try all sorts of different things. Please note the code is running perfectly. Only thing is to display one button only at a time. Each code takes more than 2 minutes to run in my system. If done in the server it may take at least 1/2 hour to run.

  • If I place the buttons on top of one another, a menu is activated by the name of Shape format. There is an option in that you can interchange the buttons like Bring forward and Send backward. But to use that function in a code I have no knowledge of it.

  • Mumps. Your code was right. Instead of button I changed it to the name of the button. That is why it was not accepting. So I recorded a macro and tried it your way and found it working. Thanks a lot. The problem now is solved.


    {ActiveSheet.Shapes.Range(Array("Button 3")).Select

    ActiveSheet.Shapes("Button 3").ZOrder msoSendBackward

    Range("K6").Select}

    ActiveSheet.Shapes.Range(Array("Button 5")).Select

    ActiveSheet.Shapes("Button 5").ZOrder msoSendBackward

    Range("K7").Select

  • READ THE FORUM RULES THEM MESSAGE ME TO LET ME KNOW THAT YOU WILL FOLLOW THEM.


    I don't understand why you need two buttons.


    The next time you post make sure that you follow our Rules or else your post will be closed.

  • I have copied and saved the rules of this forum on my desktop. In future, before posting, I will make sure that the rules are followed.

    I would like to know how to close my query once it is solved.

  • I'm glad everything worked out. :) Just a comment about your code: Recording a macro is a good start but most often the result is not very efficient. To give you an example, you don't very often need to select a cell or a range to perform an action on it.

    for example, this code:

    Code
    With Sheets("Original").Select
        Columns("A:I").Select
        Selection.Copy
        Range("A2").Select
        Sheets("Bank").Select
        Range("A1").Select
        ActiveSheet.Paste
    End With

    can be written like this:

    Code
    Sheets("Original").Columns("A:I").Copy Sheets("Bank").Range("A1")

    or:

    Code
    Range("B2:BE5000").Select
    Selection.ClearContents

    can be written like this:

    Code
    Range("B2:BE5000").ClearContents

    This would speed up the macro and make it much shorter. There are other things you could do to improve the code efficiency. Do a little research in what can be done to make a macro run more quickly. I'm sure you will find many such methods. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thank you so much Mumps. That was very kind of you. There is no end to learning in excel especially. So, whatever is related to accounts, I really try and learn the formulas and codes connected to it. Your words are encouraging and I need that inspiration. Thank you once again.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!