Consulting

Results 1 to 7 of 7

Thread: Ribbon disappearing bug / issue with getVisible and .Invalidate

  1. #1

    Ribbon disappearing bug / issue with getVisible and .Invalidate

    Hi,

    I have a custom toolbar in excel and in it I have a toggleButton. I want the state of this button to be context sensitive depending on the formatting of the selected cell(s).
    To achieve this I have a custom class module that invalidates the ribbon every time there is a selection change. This forces a refresh of the getPressed attribute of the button which runs some logic to determine what state it should be in.

    This all works fine, however I also have a getVisible tag for the entire ribbon tab which I am using as a sort of basic DRM. It checks against the username format of the company and disables the ribbon if it doesn't match.

    Now the bug I am experiencing is, if I select a cell contents by either pressing "F2" or double clicking the mouse, occasionally (but not always) the entire ribbon disappears. When the worksheet selection is changed again, the .Invalidate fires and the ribbon reappears.

    I know that if I remove the getVisible tag from the ribbon xml, I do not have this issue, so I believe it is something to do with that. Unfortunately as far as I can tell from testing it in isolation, the code always returns "enabled = True".

    As it's quite a complicated set of subs to explain, I have attached an example - does anyone have any ideas as to what is going on here?

    Example.xlsm

    EDIT: After further testing it seems to happen only with a particularly quick switch into a cell's contents occurs after a change of selection i.e
    Select a cell and hit F2 to enter it
    Hit return, immediately followed by F2 again (ribbon disappears)
    Hit return again or use mouse and the ribbon will return
    Last edited by rdekanter; 10-17-2018 at 04:40 AM.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    Not sure if this will help of not

    Look at the !!!!!!!!! comments in my markup / rewrite

    I think you might have over-complicated it

    1. I removed the class and just use Workbook events

    2. It seems 'fragile', i.e. depending on selection you may get error since Null is returned sometimes in

    Capture.JPG Capture2.JPG
    Sub CET_CenterAcrossState(control As IRibbonControl, ByRef pressed)
        Dim v As Variant
        v = (Selection.EntireColumn.HorizontalAlignment = xlCenterAcrossSelection)
        
        If Not IsNull(v) Then       '   !!!!!!!!!!!!!!!!!!
            pressed = v
        Else
            pressed = False
        End If
        
    End Sub
    3. Strictly speaking, subs in your 'EventHandler' module are 'Callbacks', just to be a nit picker

    4. I had to force CET_DisableRibbon to be True to play with it

    Sub CET_DisableRibbon(control As IRibbonControl, ByRef enabled)
        'Disable ribbon if the Windows username does not match the 9 digit UIN format
        enabled = (IsNumeric(Environ("username")) And Len(Environ("username")) = 9)
        enabled = True      '   Force
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Thanks for taking a look Paul

    re #3 I'm self taught so I realise I often don't do or explain things in the proper way - I appreciate the criticism though as if no one tells me, I won't know!
    re #4 I stripped down my actual file when creating the example but forgot that it would disable itself for anyone that actually tried to help . It also explains the random bits of unexplained code you picked up on as I clearly missed those too.

    The reason I have such a complicated method is because I will be turning the .xlsm into a .xlam addin which I can distribute to my team. That way, the toolbar is loaded every time a user opens an instance of excel, however it means the workbook event handlers will never trigger as the workbook itself isn't touched. The custom class is a workaround that I found in an example on the internet (although I admit I don't fully understand it).

    As for your method, it didn't work for me as the button didn't toggle when a cell with the "CenterAcrossProperty" was selected. I did manage to get it working however by tweaking
    v = (Selection.EntireColumn.HorizontalAlignment = xlCenterAcrossSelection)
    to
    v = (Selection.HorizontalAlignment = xlCenterAcrossSelection)
    I'll try your method in an addin and see if it works, then post back.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    OK

    I added the .EntireColumn when I was trying to figure out some of the inconsistant results depending on what was selected (or so I think I kind of remember)
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    I can confirm that it doesn't work for the reasons stated above (i.e. when loaded as an addin, the events for the addin will never fire).

    FWIW, this is where I got the original method from:
    http://www.pptfaq.com/FAQ00004_Make_..._to_events.htm

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    That is for PowerPoint, but two more Excel-oriented articles are these


    https://www.jkp-ads.com/Articles/buildexceladdin05.asp

    http://www.cpearson.com/excel/appevent.aspx
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Yes, I adapted it from PPT but it does align with the links you sent.

    I know this can be seen as a cop out (i.e. blaming someone else), but I am inclined to think this is a timing issue bug within Excel. You have to be very quick between button presses to have it happen so I think I might just live with it and hope no one notices

Posting Permissions

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