Ribbon disappearing bug / issue with getVisible and .Invalidate

[rdekanter]

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

[Paul_Hossler]

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

[rdekanter]

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.

[Paul_Hossler]

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)

[rdekanter]

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

[Paul_Hossler]

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

[rdekanter]

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

[kakocer]

You are referring to some built-in controls on the ribbon. Make sure all of them are available when Excel is open.