+ Reply to Thread
Results 1 to 11 of 11

Excel 2010 hiding commandbuttons

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    Bemidji, Minnesota
    MS-Off Ver
    Excel 2007
    Posts
    15

    Excel 2010 hiding commandbuttons

    I have several commandbuttons on a worksheet residing within rows which are sometimes filtered out and sometimes included in what the filter shows. This works fine in Excel 2007. However, one of my PCs which uses the workbook is on Excel 2010, and Excel 2010 automatically collapses the commandbuttons when their rows are filtered out, so that the next time their row is filtered in the commandbuttons cannot be seen. (If you hover over their location, it is possible to find them and restore them from 0 height to their previous height.)

    Does anyone have a solution in how to filter a row which includes a commandbutton without collapsing the commandbutton? I don't want the commandbutton to be visible when the row is filtered out.

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Excel 2010 hiding commandbuttons

    Have you set properties to move but don't size with cell? Maybe that could affect it.
    Attached Images Attached Images
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    11-27-2012
    Location
    Bemidji, Minnesota
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Excel 2010 hiding commandbuttons

    Jacc, thanks for the suggestion. However, if I set the commandbutton to move but not size with the cell, then it isn't hidden when the row its in is filtered out. I need it to filter out with the row and the only way to do that is to set it to both move and size with the cell.

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Excel 2010 hiding commandbuttons

    Perhaps you could put the commandbuttons on a userform?

  5. #5
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: Excel 2010 hiding commandbuttons

    I built a test sheet in 2007 and opened it in 2010. I cannot duplicate your problem. Nothing I tried would not restore the buttons when the rows were selected via a filter.

  6. #6
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Excel 2010 hiding commandbuttons

    For the record I have experienced similar problems as the ones bemidjipatriotbefore is talking about and I found it rather unpredictable.

  7. #7
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: Excel 2010 hiding commandbuttons

    I'm not saying I think it didn't happen, I believe it did. All I meant was that without being able to duplicate it will make it tough if not impossible to solve. If either of you have a sample that exhibits this you might consider posting it.

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Excel 2010 hiding commandbuttons

    My solution was a macro that reset the sizes of the buttons. Seems that there is a workaround to get the macro to run even though there is no "Filter event".
    http://stackoverflow.com/questions/1...nning-a-filter
    http://www.experts-exchange.com/Soft...-with-VBA.html

  9. #9
    Registered User
    Join Date
    11-27-2012
    Location
    Bemidji, Minnesota
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Excel 2010 hiding commandbuttons

    Jacc, thank you. I think this might be a workable solution. I think I can figure out how to get the resize button code to run at the right time. Would you be willing to send your code that resizes the buttons?

  10. #10
    Registered User
    Join Date
    11-27-2012
    Location
    Bemidji, Minnesota
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Excel 2010 hiding commandbuttons

    Yraen, you asked for an example as you said you couldn't replicate. Attached is a sample. I took a worksheet that was doing this and deleted most of it (including the macros and code that the buttons use) but left the first few rows including the rows with the command buttons that hide in Excel 2010 when filtered. I don't have Excel 2010 on this machine to test to confirm that it still does this on this modified sheet, but the properties are the same so I assume it will. I think Jacc's solution might be my answer but it would nice if you have any thoughts on how to stop this from happening to begin with. Thanks!
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    11-27-2012
    Location
    Bemidji, Minnesota
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Excel 2010 hiding commandbuttons

    Thanks for everyone who commented on this thread. I thought I'd provide an update on the solution I found. I haven't tried it in Excel 2010 but don't think the Excel version should matter. What I was doing was filtering out certain rows with command buttons on them depending on which command buttons I wanted to use at the time. The solution I have come up to is to have one row only with multiple command buttons located within it and use a macro to hide or unhide the command button, rather than using a macro to filter or unfilter a given row.

    ActiveSheet.Shapes("CommandButton2").Visible = True OR

    ActiveSheet.Shapes("CommandButton2").Visible = False

    So far this seems to solve my dilemna.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1