Excel interface Archives

Tabbing and Viewing Workbooks on Two Monitors

Posted on April 28, 2018 by Doug Glancy

I often have two workbooks up on two monitors. And of course I have a bunch of other programs open. I used to struggle to view both workbooks at the same time. Using Ctrl + TAB hides the first one as it shows the second one. Two workbooks tiled side-by-side on one monitor have the same issue. The solution I’ve found is to tab “backwards” using Shift + Ctrl + TAB.

The Ctrl + TAB Problem

Below I’m clicking Ctrl + TAB. As soon as the next workbook appears the first one goes away. It seems to go behind the Windows Explorer Window:

Ctrl + TAB

Shift + Ctrl + TAB to the Rescue

Below, I’m using Shift + Ctrl + TAB to cycle “backwards” through the workbooks. For whatever reason, now the first workbook stays visible, and I can see both:

Shift + Ctrl + TAB

This is one of my favorite keyboard tricks. Any explanations of what’s going on would be greatly appreciated, as would any better solutions.

Creating a History Navigation System

Posted on April 7, 2018 by Doug Glancy

I’m working on an Excel workbook that’s a database dictionary. It has a data-validation list of all the tables in the database. Picking a table in the list updates a query that returns info about that selection. I’d like to keep a history of tables I’ve already looked at and get back to them quickly by clicking buttons or picking them from a history list. So I came up with a history navigation system.

The simple example for this post lets you quickly return to years of National League home run stats that you’ve already viewed.

History Navigator overview

What Kind of History do I want?

Two familiar examples of navigation history are those in browsers and file explorers. I’m in an out of Windows File Explorer all day long and use the history a lot. I like the way it works, but it seems silly that it includes the same folder multiple times.

File Explorer history

Firefox history doesn’t do that and just lists each visited page once. The thing that I don’t necessarily like is that if you hit the back button and then do a new search you lose the page that you “backed” from.

Firefox history

My Philosophy of History

After thinking about the above patterns for at least five minutes, I decided how I want history to work:

All previous choices made are included in the history for as long as the workbook is open.
Each previous choice is only included once.
If it’s already in the list, it’s moved to the most recent position when it’s chosen again.

The Code

It’s pretty simple, so I’m not going to go into it. You can see it by downloading the sample below. I used Form buttons so that I could assign the same subroutine to both of them without having to use WithEvents. Most of the logic has to do with updating the data validation list that has the history and updating the Prev and Next buttons. I’ve implemented this code in my data dictionary and it’s working well.

navigation history

Download

Here’s a sample workbook with the setup and code. Let me know what you think!

customUI Ribbon XML Editor

Posted on February 27, 2018 by Doug Glancy

I’ve created a new addin for editing and validating Ribbon customUI XML. It’s a form that lets you modify ribbons in workbook and addins. Here’s a couple of screenshots:

form with highlighted error

form_with tips

YouTube Video and customUI Ribbon XML Editor

Here’s a YouTube video! yoursumbuddy’s first!

Here’s the link to the yoursumbuddy customUI Ribbon Editor page, where you can download it and read more.

Please let me know what you think.

Some Things I Learned Lately

Posted on March 9, 2017 by Doug Glancy

Seems like every day I pick up something new. So I thought I’d make a quick list of stuff I’ve learned lately.

1) XMLAGG in Teradata
Teradata’s handy XMLAGG function lets lets you flatten a column of data into a delimited list, thereby avoiding recursion. Oracle, to which I’m migrating, also has an XMLAGG function but the closer, and better-named, equivalent seems to be LISTAGG. The Teradata documentation is consistently terrible, so instead I’ll link to this Stack Overflow answer from the worthy dnoeth.

2) 64-bit Office Text File Connection Strings
While updating a fancy data-sucking addin I got an error message that my data connection was broke. Turns out MS changed the ODBC connection string for 64-bit ever so slightly from:

Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;

Driver=Microsoft Access Text Driver (*.txt, *.csv);Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;

There’s two differences. The addition of the word “Access” was quickly apparent when looking at this site. The second one took me some time to spot. Can you see it? Yup, they changed the semicolon after “*.txt” to a comma. I think it looks a lot better.

3) Format vs WorksheetFunction.Text in VBA to Mimic Cell Formats
I’ve done a couple of posts that attempt to give you a sneak preview of how different formats will look when applied to cells. I was using my ActiveCell Viewer to preview different date formats for a cell. The Viewer used the VBA Format function. I noticed that in some cases it returned text that isn’t what you get in a cell with that formatting.

For instance, below I’ve applied a formatting of an increasing number of “d”s to a date. With formatting of two to four “d”s the two outputs match:

format differences 1

However with 5 or 6 “d”s the VBA function begins to return a weird result that doesn’t match what you’d see in a cell with that formatting:

format differences 2

You can see below that a cell formatting of “dddddd” still returns “Friday,” just like WorksheetFunction.Text. In fact if you close the Format Cells dialog and re-open it, you’ll see that the formatting has been reset to “dddd”.

format differences 3

I’ve since fixed my Activecell Viewer and added some features. I’ll try to remember to post the improved version sometime.

4) You Can Undo a Pivot Table Refresh
Who knew? All these years I assumed you couldn’t. And then I tried it.

pivot table unrefresh

5) Pivot Table Grouped Number Ranges, Once Sorted, Can’t Be Forced Back to Numeric Order
At least I can’t figure out how.

pivot number ranges grouped and sorted

Can you?

Filtering Pivot Table Value Fields

Posted on May 21, 2016 by Doug Glancy

In the old days, before I knew better, when I needed to filter a pivot table Value field I’d do it by using by throwing an autofilter from the Data menu on it:

Pivot with Autofilter

It made me feel dirty but I didn’t know any other way to filter pivot table value fields. Clearly pivot tables provided no filter in the Values columns:

Pivot with no filter

And then one day I read this SuperUser answer by mtone and never looked back. If you don’t know how to apply a Value Filter to a non-Value pivot field I recommend reading it right now:

SuperUser Value Filter answer

Good wasn’t it? I started to write a post about this and realized this was the best explanation I’ve seen. I especially like that mtone explains how choosing different fields to apply the filter to will result in different levels of aggregation, and will change your results.

One Value Field Filtering Oddity

I was prompted to write this post after answering Bijan’s question on Stack Overflow. I quickly helped solve his problem but was perplexed because his original issue was that he was applying a value filter to the value field itself. As I said at the beginning, this isn’t possible. Turns out that’s not quite true. After an extended chat he showed me how he did it.

Before you can do this, you first need to use the field as a Row, Column or Report field. Simply dragging it to the Row area of the Show Filter dialog and then to the Values area will do the trick. Once you do so, you can click the down arrow next to the field name in the pivot’s Show Fields dialog:

show fields with dropdown

At that point you can pick the various Value filters.

value field Value Filters

As Bijan discovered though this doesn’t actually do anything. No filtering occurs (and if it did, it wouldn’t be an aggregate filter anyways). And when you drag the field back to a non-value position it, the filter that is, goes away. Weird and bug-like.

Editing Shape Points for No Good Reason

Posted on April 9, 2016 by Doug Glancy

Over at Bacon Bits, Mike Alexander has a nice post out yesterday on editing shape points to create custom graphics. It shows how to use a shape’s Edit Points command to create interesting dashboard icons. I haven’t used Edit Points for years, but his post inspired me to fool around with them. I ended up with a bit of code for Editing Shape Points for No Good Reason.

In Mike’s post he shows how you can edit points to modify a half-circle into a more interesting shape. His edit of a half-circle looks something like this (only better):

semicircle to wavy

I thought that was pretty cool, so then I dragged the top below the bottom:

semicircle to wavy

That was fun. Now how about some facial reconstruction for Smiley?

smiley to pointy

At this point of course I had to learn how to program these edits in VBA. The result is code that randomly messes with the edit points, along with colors:

Pointless Point Editing Code

Sub EditPointMadness()
Dim shp As Shape
Dim shpNodes As ShapeNodes
Dim CenterX As Long
Dim CenterY As Long
Dim CurrXValue As Long
Dim CurrYValue As Long
Dim ws As Excel.Worksheet
Dim pointsArray As Variant
Const PointOffset As Long = 200

Set ws = ActiveSheet
If ws.Shapes.Count = 0 Then
ws.Shapes.AddShape msoShapeSmileyFace, 300, 300, PointOffset, PointOffset
Exit Sub
End If
Set shp = ws.Shapes(1)
CenterX = shp.Left + (shp.Width / 2)
CenterY = shp.Top + (shp.Height / 2)
Set shpNodes = shp.Nodes
With shpNodes
.Insert WorksheetFunction.RandBetween(1, .Count), msoSegmentCurve, msoEditingAuto, _
WorksheetFunction.RandBetween(CenterX - PointOffset, CenterX + PointOffset), _
WorksheetFunction.RandBetween(CenterY - PointOffset, CenterY + PointOffset), _
WorksheetFunction.RandBetween(CenterX - PointOffset, CenterX + PointOffset), _
WorksheetFunction.RandBetween(CenterY - PointOffset, CenterY + PointOffset), _
WorksheetFunction.RandBetween(CenterX - PointOffset, CenterX + PointOffset), _
WorksheetFunction.RandBetween(CenterY - PointOffset, CenterY + PointOffset)
If Timer Mod 2 = 0 Then
pointsArray = .Item(WorksheetFunction.RandBetween(1, .Count)).Points
CurrXValue = pointsArray(1, 1)
CurrYValue = pointsArray(1, 2)
.SetPosition WorksheetFunction.RandBetween(1, .Count), _
CurrXValue + WorksheetFunction.RandBetween(-PointOffset, PointOffset), _
CurrYValue + WorksheetFunction.RandBetween(-PointOffset, PointOffset)
shp.Fill.ForeColor.RGB = WorksheetFunction.RandBetween(1, 10000000)
shp.Line.ForeColor.RGB = WorksheetFunction.RandBetween(1, 10000000)
End If
If Timer Mod 5 = 0 Then
.Delete WorksheetFunction.RandBetween(1, .Count)
End If
End With
End Sub

Every time you run the code above it adds, deletes and/or modifies another point. After a couple of times you get what I like to call “Picasso Smiley”:

smiley picasso

A few more and Smiley is getting blown into the next dimension:

smiley in the next dimension

Hopefully the code above is pretty straightforward. It leaves a few of the settings unrandomized, chiefly whether the new node is straight, shaped or a corner.

One question. How to refresh Excel between shape format changes?
One version of this code had a loop that edited the points every half second. But try as I might I couldn’t get the screen to update and show those changes. The changes would only appear after the code was finished, making a loop pointless. If anybody knows how to do this, please let us know (thereby adding some useful content to this post).

Download
Here’s a workbook with the the code and a couple of buttons to run it.

smiley edit points

Filter by Selected Cell’s Value – Bug With Blanks

Posted on April 30, 2015 by Doug Glancy

I’ve found a bug in Excel 2010 that I don’t find described at all when I search the internet. It involves the Filter by Selected Cell’s Value command and blank cells.

The Issue

In Excel 2010 when I select a blank cell in a table, or a non-table and then choose “Filter by Selected Cell’s Value” from the right-click menu, the column is filtered by what look like Chinese and non-printable characters. It occurs for me every time in a Table. In an informal list it sometimes only occurs after filters are applied.

Testing

I’ve seen the issue on all five Excel 2010 computers I’ve tried, all with Windows 7. I tried it on one computer with Office 2013 and couldn’t replicate the bug.

How to Replicate It

Create a table like this one. It can actually be just one column:

Right-click in one of the blank cells and choose Filter > Filter by Selected Cell’s Value. (The mouse-averse can do Menu Key, E, V as described in this wmfexcel post).

When I do this all rows are filtered away and I can see the odd filter criteria in the little tooltip that appears:

This image show the filter dropdown and the Number Filters dialog (if you do column A you’ll see a Text Filters dialog instead):

What I’d expect, and see in Excel 2013, is that the (Blanks) item in the dropdown would be checked.

You can see that there are a mix of non-printing characters and what Google Translate sometimes recognizes as Chinese characters. I see different character combinations on different computers. Here’s one of my home computers:

Workarounds

The obvious one is to select “(Blanks)” in the filter dropdown. Another is to upgrade to Excel 2013.

A partial one is to use add the “Autofilter” control to the Ribbon. This only works for non-Table lists though. With a Table it only turns the filters on and off. I think this control goes back to Excel 2003 – you can add it to a right-click menu by using MenuRighter and choosing Add Command > Data > Autofilter.

So, Did Everybody Else Already Know This?

I’m always hesitant to cry “bug,” but this seems pretty clear. My other fear is looking silly for announcing something everybody already knew. Feel free to comfort me, or not.

I haven’t tried it in Excel 2007 yet. If anybody wants to that would be great.

Missing Addin User Warning

Posted on April 23, 2015 by Doug Glancy

I develop workbooks that require an addin for some of their functionality. For example, a set of school staffing models whose addin allows for selecting data sources, building pivot tables and saving timestamped copies. I like this structure because it’s very maintainable – it’s much easier to fix a bug or add a feature in one addin than it is to try to track down a bunch of workbooks and update their VBA. Easier on me and better for my users. Of course, people’s computers get re-imaged or replaced and IT departments are unlikely, to say the least, to re-install a VBA addin. Folks often don’t realize their addins are gone and that’s why their model is broken. Yesterday I got an idea about how to warn users about a missing addin. I’m still tinkering with it, but the idea is quite simple.

Use a UDF in the Addin

What I did was create the world’s simplest UDF and put it in the addin:

Public Function IsAddinLoaded() As Boolean
IsAddinLoaded = True
End Function

It doesn’t have to be boolean. It could be a string and return “Cult of the Flying Spaghetti.” That’s because it’s only purpose is to fail when it’s not there.

Missing Addin = Missing UDF = opportunity for IsError formula. So, for example, in cell A1 of the addin-dependent workbook put something like:

="Ye Olde Spreadsheet " & IF(ISERROR(isaddinloaded()*TODAY()),"requires the Green Eyeshade Addin!
Please install it.","is Good to Go")

Then add a little conditional formatting based on the missing addin condition and you get something like this. (I tried referring to the UDF directly in the conditional formatting, but it looks like that’s not allowed.)

The formula contains a volatile function: TODAY. That’s so if the addin is uninstalled the error is activated with the next change to the worksheet. I’d like to avoid the volatile function and I’ve tried to do this in the VBA itself, but haven’t figured out anything yet.

The message does change to the non-warning one as soon as the addin is installed.

The addin a very simple one made up just for this post. It’s called Green Eyeshade and it’s for modernizing old spreadsheets like this one. Its ribbon tab is shown when a workbook meeting certain criteria is opened. Here’s the ribbon tab:

And here’s the modernization button in action (wonderful what you can find by googling “Peltier pie charts”).

This idea could also be applied to the problem of warning users to enable VBA when opening xlsm files. I’ve never been a big fan of the “hide everything in the workbook except a warning page” although I do like Mike Alexander’s clear explanation. Anyways, something like this might work as an alternative in that case too.

Download

Here’s a zip file with the xlam addin and the target “olde spreadsheet” xlsx file.

MenuRighter Update!

Posted on April 11, 2015 by Doug Glancy

MenuRighter is my addin for modifying right-click menus, also known as context menus. It’s been around for three years and downloaded a few thousand times. It’s one of the cooler things I’ve ever made. I’ve just finished making some changes to it.

With MenuRighter you can turn this…

into this…

With four clicks I removed the Cut, Copy, Paste and Clear Contents buttons from the List Range Popup (Table) menu. With a few more I added my entire Personal Macro workbook (the cleverly named “myBar”) and its Tables sub-menu. Using MenuRighter’s new search functionality I quickly found and added two filter options along with the “Select Visible Cells” button.

Here’s what MenuRighter looks like. It’s divided into the Source menus and controls on the left and Target ones on the right. The Target menu is the one you’re adding controls to:

Removing and Adding Controls
And here’s a couple of quick videos showing how I did the deleting from, and adding to, in the Table context menu. This first one shows how to delete the controls – just select one and click Remove. For even faster removal you can double-click the control in the list:

Here I’m selecting the spot to insert the “Clear Selected Filter” and “Filter by Selected Cell’s Value” controls in the Target listbox. Then I filter in the Source combobox, click the Add button, and “hey Presto” they’re added. For even faster addition you can double-click the control to be added.

In both examples above, I clicked the “Apply Changes” button to actually modify the context menu. Up until then the “Reset to Current” button will revert the Target listbox to the context menu’s current setup. And at any time you can use the “Reset to Default” button to reset the menu back to Excel’s default.

Moving Controls

You can see in the clip above that controls are added above the selected control in the target menu. (If no control is selected the new control is added at the top. You can also use the Up, Down, Top and Bottom buttons to rearrange and controls in the target menu.

Settings

“But wait” I hear you say, “how did you know to choose to the “List Range Popup” menu to modify the context menu for a Table. The answer is MenuRighter’s handy “Show Labels on Menus” checkbox. It temporarily adds a control with the name and index to the bottom of each right-click menu.

There’s two other setting checkboxes. “Double-type dots” has to with MenuRighter convention for showing the Menu..Sub-menu..Control hierarchy. For instance, the Open control on the File is shown as Add Command..File..Open. If your search filter involves one of those “double-dots” I didn’t want you to have to type both, so, with that option checked, typing one dot gets you two.

The other Setting “Match Source Names” has to do with typing in the Source dropdown list at the top. That list contains all the toolbars that you can filter through in your search for controls. With the option checked, the combobox autofills the dropdown with the closest-matching item in the list. With it unchecked, it just filters based on whatever you type. It’s hard to describe, so just try it out.

The “Execute” Button
One other helpful new feature is the “Execute” button. When you’ve selected a control in the source list, and if that control is enabled, click Execute to run that control. For example, if you’ve selected Add Command..File..New and you click Execute, you’ll get a new workbook. (Pro Tip – choose the New command without three dots).

Search Tips

If you’ve used MenuRighter before, you’ll see that the main changes involve finding controls. The old version uses more of an Explorer type model of drilling down to controls. This new one lets you filter, with two big advantages: it’s faster and it’s more helpful if you have no idea where to start looking.

For example, the “filter by Selected Cell’s” value is a new control since 2003, so only shows in three context menus. Start typing “Filter by” into the Source combobox and you’ll see your choices instantly. On the other hand the Camera tool has been around (and hidden) forever. Type in “camera” and you’ve found it! “Freeze Panes” is another one that’s easy to find this way.

If you just want to scan through menus you can do it by scrolling through the seventy-or-so menus in the Source combobox. Or, you can choose menus in the Target combobox and click the “Show Current” button. Here’s an example of showing the Add Command controls. (Add Commands represents all the controls that were available in Excel 2003’s menu modification dialog):

Looking for A Few Testers

I’ll be posting this new version soon, but in the meantime it would be great to have a few folks try it out. If you’re interested please let me know in the comments section or through the contact form.

Tweaking View Side by Side

Posted on March 15, 2015 by Doug Glancy

I use View Side by Side a lot. That means I spend a bit of time fiddling with the two windows because I don’t always want each to take up half the space. I often make one smaller and then adjust the second to have it fill the rest of the screen. This weekend I set about tweaking View Side by Side to adjust one window when the other is resized.

Along the way I learned a few interesting things. The most important to this code was figuring out how tell if View Side by Side is activated. I also noticed some issues with View Side by Side in Excel 2013.

Below is a screenshot of what happens when you resize a window in View Side by Side. You can see that it needs to be followed up with some more fiddling to snug the lower window up against the upper one:

The code I came up with is in an application-level event. It uses a function to test whether View Side by Side is active. It’s designed to work in Excel 2010 (and 2007, although untested) as well as 2013. The 2013 tweaks were a little trickier, because Application.Height returned the same thing as Window.Height in 2013. This means the code needs to store the lower windows top location before resizing it.

You’ll also note that I used On Error Resume Next more freely than normally. After all we’re just resizing windows here, and it’s easier than testing for odd things like heights less than zero:

Public WithEvents app As Excel.Application

'application-level event in a class module
Private Sub app_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)
Dim UpperWindow As Excel.Window
Dim LowerWindow As Excel.Window
Dim OldLowerWindowTop As Double
Dim LowerWindowChange As Double

'See function below
If SideBySideOn Then
With Application
.EnableEvents = False
'assign the two windows,
'based on which is uppermost
If .Windows(1).Top <= .Windows(2).Top Then
Set UpperWindow = .Windows(1)
Set LowerWindow = .Windows(2)
Else
Set UpperWindow = .Windows(2)
Set LowerWindow = .Windows(1)
End If

'easy way to deal with heights less than zero, etc.
On Error Resume Next
'for Excel 2010 could just use Application.Height
'but in Excel 2013 each window is its own Application
If ActiveWindow.Caption = UpperWindow.Caption Then
OldLowerWindowTop = LowerWindow.Top
LowerWindow.Top = UpperWindow.Top + UpperWindow.Height
LowerWindowChange = LowerWindow.Top - OldLowerWindowTop
LowerWindow.Width = UpperWindow.Width
LowerWindow.Height = LowerWindow.Height - LowerWindowChange
Else
UpperWindow.Height = LowerWindow.Top
UpperWindow.Width = LowerWindow.Width
End If
On Error GoTo 0
.EnableEvents = True
End With
End If
End Sub

This code only responds to resizes involving the two horizontal borders in the middle, as well as the right-hand borders. So, if you drag the top border of the upper window nothing adjusts.

Application-Level Event

The sample workbook sets an application-level class in its Workbook_Open event. I’ve posted on this before and there’s lots of good info out there, like this Chip Pearson page.

How Do You Tell if View Side by Side is Active?

That’s an interesting question! Here’s what I tried:

First, I looked for some type of setting, like Application.ViewSideBySideEnabled. I can’t find one. Instead there’s two methods: CompareSideBySideWith and the rather drastic-sounding BreakSideBySide.
It would be nice if you could just get the state of the Ribbon’s View Side by Side button. That would tell you whether it’s active or not. But I don’t know of any way to do this.
You can, however, check the state of Excel 2003-style command buttons. So I tried looking at
Application.CommandBars(1).Controls("Window").Controls("Compare Side By Side With").State

to check whether it’s pressed. It turns out that control – ID 7698 – has no state. Instead it switches captions from “Compare Side by Side With” (followed by a workbook name if there’s only one choice) to “Close Side By Side.”
Based on #3 I considered getting the state by checking whether the first five letters are “Close” or “Compa.” But of course that wouldn’t work for folks with non-English Excel versions, and I generally shy away from using labels when I can use ID’s.
So, finally I decided to use the state of the “Reset Window Position” button. This button is only enabled when View Side by Side is active.

This leads to a one-line function that checks that button’s status using its ID of 7874. There is one final wrinkle, which is that this button doesn’t appear by default in the Excel 2003 “Window” toolbar. By default it lives only in the “Add Command” toolbar which was the one that appeared in 2003 when you modified a toolbar. No problem. We can find number 7874 wherever it lives by searching through all the commandbars using FindControl. Here’s the function:

Function SideBySideOn() As Boolean
'Reset Window Position
SideBySideOn = CommandBars.FindControl(ID:=7874).Enabled
End Function

Here’s how it looks with the code running:

Other Stuff Learned Along the Way

If you click the Reset Window Position button with the lower window selected, it moves to the top. I’d never noticed that.
There is no Window.Move event
View Side by Side in Excel 2013 is harder to use. This makes sense, since one of the biggest changes in Excel 2013 is the change to a Single-Document-Interface, making it easier to separate workbooks to multiple monitors. However it’s hard to grab the right edge of a workbook inside View Side by Side. And, as noted above, Application.Height and Window.Height now seem to be the same thing. This makes sense most of the time, but not when you are in View Side by Side mode and trying to compare the height of one of the workbooks/windows to the overall height.

Download!

Here you are my friend.