+ Reply to Thread
Results 1 to 8 of 8

Custom ribbon checkboxes do not retain values when file is closed.

  1. #1
    Registered User
    Join Date
    07-21-2010
    Location
    Bakersfield, CA
    MS-Off Ver
    Excel 2007
    Posts
    18

    Custom ribbon checkboxes do not retain values when file is closed.

    I have a group of checkboxes on a custom ribbon that I use to hide certain worksheets. When the file is closed and reopened, the checkboxes reset to their default value of false. I would like the checkboxes to retain the values they had when I closed the file.

    How do I change the values of the checkboxes using vba? Thanks in advance!
    Attached Images Attached Images
    Last edited by ericsastud; 10-29-2010 at 12:14 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,859

    Re: Custom ribbon checkboxes do not retain values when file is closed.

    Hi Eric,

    I'd say you need to save the status of the check boxes somewhere and reset them to this saved value when you first open the workbook. I'd need an example file to examine how and where this should be done.

  3. #3
    Registered User
    Join Date
    07-21-2010
    Location
    Bakersfield, CA
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Custom ribbon checkboxes do not retain values when file is closed.

    Here is an example file. Look on the Inputs and Ratios Tab on the ribbon. The checkboxes show/hide sheets. If have Sheet 1 and Sheet 3 are visible when the file is opened, how do I make Checkbox 1 and Checkbox 3 be checked?
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,859

    Re: Custom ribbon checkboxes do not retain values when file is closed.

    Hi eric,

    I can't seem to find a way to make those boxes checked or not. It looks like the Ibuttoncontrol is a Office Object and is read only. I can read the Tag and Id of the control but it doesn't look like it is checkable using Excel VBA code. A mouse click seems to be the only way I can get them to change.

    On http://msdn.microsoft.com/en-us/libr...oncontrol.aspx it claims it is not supported using VBA. I think that is the final answer.

    I wonder if other programmers have an answer.

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Custom ribbon checkboxes do not retain values when file is closed.

    You need to set up a callback function for the getPressed attribute of each checkbox that returns true or false based on the relevant sheet visibility.
    Remember what the dormouse said
    Feed your head

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Custom ribbon checkboxes do not retain values when file is closed.

    See revised attachment for example - you will need to check the new CustomUI XML too.
    Attached Files Attached Files

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Custom ribbon checkboxes do not retain values when file is closed.

    Nice work Romper.

    I think the actual code could be a little shorter
    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Custom ribbon checkboxes do not retain values when file is closed.

    I didn't even look at the existing code to be honest, but you are of course right.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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