ribbon – Daily Dose of Excel

We’ve all been there. I group a few sheets, change several things at once, and pat myself on the back for being so efficient. A few changes later, I realize that the sheets are still grouped and that I’m an idiot. I finally decided to do something about it and I happened on this old post from Contextures. Debra asks

What would you like Excel to do, to make grouped sheets more noticeable?

I’d like a warning, but I’d like it to be non-modal. That is, I don’t want it to interrupt me.

What about a hideous Ribbon tab that appears when you group sheets?

First, I used the CustomUI editor to add some XML to my workbook.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="GroupAlertRibbonLoad">
	<ribbon startFromScratch="false" >
		<tabs>
			<tab id="tabGroupAlert" label="Grouped Sheets Warning" getVisible="tabGroupAlert_GetVisible" >
				<group id="grpGroupAlert" label="Grouped Sheets Warning" >
					<button id="GA1" size="large" imageMso="ColorRed" />
					<button id="GA2" size="large" imageMso="ColorYellow" />
					<button id="GA3" size="large" imageMso="ColorRed" />
					<button id="GA4" size="large" imageMso="ColorYellow" />
					<button id="GA5" size="large" imageMso="ColorRed" />
					<button id="GA6" size="large" imageMso="ColorYellow" />
					<button id="GA7" size="large" imageMso="ColorRed" />
					<button id="GA8" size="large" imageMso="ColorYellow" />
					<button id="GA11" size="large" imageMso="ColorRed" />	
					<button id="GA12" size="large" imageMso="ColorYellow" />
					<button id="GA13" size="large" imageMso="ColorRed" />
					<button id="GA14" size="large" imageMso="ColorYellow" />	
					<button id="GA15" size="large" imageMso="ColorRed" />
					<button id="GA16" size="large" imageMso="ColorYellow" />	
					<button id="GA17" size="large" imageMso="ColorRed" />
					<button id="GA18" size="large" imageMso="ColorYellow" />	
					<button id="GA19" size="large" imageMso="ColorRed" />
					<button id="GA20" size="large" imageMso="ColorYellow" />	
					<button id="GA21" size="large" imageMso="ColorRed" />
					<button id="GA22" size="large" imageMso="ColorYellow" />	
					<button id="GA23" size="large" imageMso="ColorRed" />
					<button id="GA24" size="large" imageMso="ColorYellow" />	
					<button id="GA25" size="large" imageMso="ColorRed" />
					<button id="GA26" size="large" imageMso="ColorYellow" />	
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

<tabs>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

It’s a couple dozen buttons with alternating colors. It appears whenever a sheet is activated and sheets are grouped. Here’s the code in a standard module

Public goRibbon As IRibbonUI

Private Const msMODULE As String = "MCallbacks()"

'Callback for customUI.onLoad
Sub GroupAlertRibbonLoad(ribbon As IRibbonUI)

    Set goRibbon = ribbon
    
End Sub

'Callback for tabGroupAlert getVisible
Sub tabGroupAlert_GetVisible(control As IRibbonControl, ByRef returnedVal)
    If Not ActiveWindow Is Nothing Then
        returnedVal = ActiveWindow.SelectedSheets.Count > 1
        If returnedVal Then
            goRibbon.ActivateTab "tabGroupAlert"
        End If
    End If
    
End Sub

Public goRibbon As IRibbonUI

Private Const msMODULE As String = "MCallbacks()"

'Callback for customUI.onLoad

Sub GroupAlertRibbonLoad(ribbon As IRibbonUI)

Set goRibbon = ribbon

End Sub

'Callback for tabGroupAlert getVisible

Sub tabGroupAlert_GetVisible(control As IRibbonControl, ByRef returnedVal)

If Not ActiveWindow Is Nothing Then

returnedVal = ActiveWindow.SelectedSheets.Count > 1

If returnedVal Then

goRibbon.ActivateTab "tabGroupAlert"

End If

End Sub

The onLoad procedure sets up a global Ribbon variable. The getVisible procedure controls whether you can see the custom tab. If the count of SelectedSheets is greater than one, returnedVal is set to True and that makes the tab visible. The If block shows the tab if it’s visible using the ActivateTab method.

In the ThisWorkbook module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    goRibbon.Invalidate
    
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

goRibbon.Invalidate

End Sub

When a sheet is activated, the Ribbon is invalidated and the getVisible procedure is forced to run again.

The next step would be to put this code in an add-in with a class module and application level events to monitor all workbooks.

You can download GroupSheetAlert.zip

Now that Excel 2003 is almost completely out of my life, it’s time to get my many add-ins ribbonized. Here’s what my UIHelpers ribbon looks like.

I used the Custom UI Editor and it was horrible. Did they put the VBE team on that project? I think I’ll find an alternative for the next add-in I convert. I don’t mind XML so much. The absolute worst part of creating a ribbon is finding a suitable button image. I downloaded Jim Rech’s add-in from Ron’s site. I scrolled through some button pages, but with 3,500 images, that wasn’t going to work.

Eventually, I changed the IsAddin property so I could see the list of image names. Then I filtered the names based on some keywords. Once I had a good candidate, I’d go back up to the ribbon, find that button, and look at it. This was much better than browsing through all of the pages hoping I’d find something suitable.

As you can imagine, I couldn’t leave well enough alone. I modified Jim’s code so I could filter the images based on their names directly on the ribbon. Jim had a nice system for what he wanted to do and I spent far too much time trying to shoehorn this new feature into that system. After a while, I started, more or less, from scratch. I should have done that from the start.

I started by adding a textbox to the ribbon where the filter term will go. It looks like this:

I don’t know what all that means, I just copied Jim’s and changed a few things. For the control handling part, I created two variables: mcolFiltered and mcolShown. mcolFiltered is a collection that holds all of the controls that match the filter. If there’s no filter, it simply holds all of the controls. When the ribbon loads, it sets up some module-level variables.

Private Sub FilterImagesRibbonLoaded(ribbon As IRibbonUI)
    
    Set mRib = ribbon
    mlStart = 1
    mlSize = eButtonSize.Normal
    ResetListToUse
    msWhat = vbNullString
    SetButtons
    
End Sub

Private Sub FilterImagesRibbonLoaded(ribbon As IRibbonUI)

Set mRib = ribbon

mlStart = 1

mlSize = eButtonSize.Normal

ResetListToUse

msWhat = vbNullString

SetButtons

End Sub

mlStart determines which member of the collection to show first. ResetListToUse gets the button names from a spreadsheet. msWhat is the filter term – set to nothing to start.

Private Sub SetButtons()
    
    Dim i As Long
    
    Set mcolShown = New Collection
    Set mcolFiltered = New Collection
        
    For i = LBound(mvaButtons, 1) To UBound(mvaButtons, 1)
        If InStr(1, mvaButtons(i, 1), msWhat, vbTextCompare) Then
            mcolFiltered.Add mvaButtons(i, 1), mvaButtons(i, 1)
        End If
    Next i
    
    If mlStart + ButtonsShown > mcolFiltered.Count Then
        mlStart = mcolFiltered.Count - ButtonsShown + 1
    End If
    
    If mlStart < 1 Then
        mlStart = 1
    End If
    
    For i = mlStart To mcolFiltered.Count
        mcolShown.Add mcolFiltered(i), mcolFiltered(i)
        If mcolShown.Count >= ButtonsShown Then Exit For
    Next i
    
End Sub

Private Sub SetButtons()

Dim i As Long

Set mcolShown = New Collection

Set mcolFiltered = New Collection

For i = LBound(mvaButtons, 1) To UBound(mvaButtons, 1)

If InStr(1, mvaButtons(i, 1), msWhat, vbTextCompare) Then

mcolFiltered.Add mvaButtons(i, 1), mvaButtons(i, 1)

End If

Next i

If mlStart + ButtonsShown > mcolFiltered.Count Then

mlStart = mcolFiltered.Count - ButtonsShown + 1

End If

If mlStart < 1 Then

mlStart = 1

End If

For i = mlStart To mcolFiltered.Count

mcolShown.Add mcolFiltered(i), mcolFiltered(i)

If mcolShown.Count >= ButtonsShown Then Exit For

Next i

End Sub

SetButtons handles what’s shown. First mcolFiltered is populated with all the control names that match the filter. Then mlStart is validated to make sure it’s within the range. Lastly, mcolShown is populated with which portion of mcolFiltered is on display. Most of the rest of the code just sets one or more module-level variable and calls SetButtons. Here’s some more code, just for fun.

Private Function ButtonsShown() As Long
    
    If mlSize = eButtonSize.Normal Then
        ButtonsShown = eButtonShow.Normal
    Else
        ButtonsShown = eButtonShow.Large
    End If
    
End Function

Private Sub EditboxEntry(control As IRibbonControl, text As String)
    
    Dim lNum As Long
    
    lNum = CLng(Val(text))
    
    If lNum <= 0 Then
        mlStart = 1
    ElseIf lNum >= mcolFiltered.Count - ButtonsShown + 1 Then
        mlStart = mcolFiltered.Count - ButtonsShown + 1
    Else
        mlStart = lNum
    End If
    
    SetButtons
    mRib.Invalidate

End Sub

Private Sub SizeCBHandler(control As IRibbonControl, ByRef returnedVal)
    
    If returnedVal Then 'Large checkbox checked
        mlSize = eButtonSize.Large
    Else
        mlSize = eButtonSize.Normal
    End If
    SetButtons
    mRib.Invalidate
    
End Sub

Private Sub GetNextGroup(control As IRibbonControl)
    
    If ShiftDown Then
        mlStart = mcolFiltered.Count - ButtonsShown
    Else
        mlStart = mlStart + ButtonsShown
    End If
    SetButtons
    mRib.Invalidate

End Sub

Private Function ButtonsShown() As Long

If mlSize = eButtonSize.Normal Then

ButtonsShown = eButtonShow.Normal

Else

ButtonsShown = eButtonShow.Large

End If

End Function

Private Sub EditboxEntry(control As IRibbonControl, text As String)

Dim lNum As Long

lNum = CLng(Val(text))

If lNum <= 0 Then

mlStart = 1

ElseIf lNum >= mcolFiltered.Count - ButtonsShown + 1 Then

mlStart = mcolFiltered.Count - ButtonsShown + 1

Else

mlStart = lNum

End If

SetButtons

mRib.Invalidate

End Sub

Private Sub SizeCBHandler(control As IRibbonControl, ByRef returnedVal)

If returnedVal Then 'Large checkbox checked

mlSize = eButtonSize.Large

Else

mlSize = eButtonSize.Normal

End If

SetButtons

mRib.Invalidate

End Sub

Private Sub GetNextGroup(control As IRibbonControl)

If ShiftDown Then

mlStart = mcolFiltered.Count - ButtonsShown

Else

mlStart = mlStart + ButtonsShown

End If

SetButtons

mRib.Invalidate

End Sub

Here’s the ribbon showing all of the controls with “filter” in their name.

As verbose as the image names are, they don’t contain enough information to do a proper search. Searching for “filter” works well enough, but if you want something related to money, you won’t find one search term that gets the job done. We need some meta data around each of the images. The first image is of a zero and is named “_0”. That image needs to turn up for searches such as zero, nil, number, digit, telephone, and probably some others. With 3,500 images, I’m not too interested in filling out all of that meta data. We need to crowd source it. Or hire some poor people.

Come to think of it, all those captchas that you have to type to prove you’re human are a lot of wasted effort. I should make a captcha system that shows a picture and asks the user to type two words that come to mind. I won’t actually check the words against anything, but I’ll use them to build my meta database for these images. Sure the captcha won’t work, but by the time people figure that out, I’ll already have my data. Brilliant.

You can download RibbonButtons.zip

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Tag: ribbon

Grouped Sheets Warning

Finding Ribbon Images