Listbox bulk refresh won’t redraw if called by Listbox click.

I’ve been playing around with ListBoxes recently. Or more to the point, they’ve been playing around with me. After much staring and swearing, I’ve found that you can’t get a ListBox to repaint itself when feeding it an entire list in one go in response to a Listbox_Click event in that same ListBox. It does however repaint correctly if you run the exact same code from somewhere else…say a CommandButton-initiated action.

By new list, I’m talking about populating the ListBox with an array via the Listbox.list=SomeArray approach. The .AddItem method works fine. So there’s always that: you can simply remove all items one by one, then add all the new items one by one. But that’s really slow: on my laptop it takes something like 15 seconds to add 10,000 items to that listbox. And it gets slower the more you add. And you can probably double that, if you need to replace each and every one of those items with something else. Whereas the Listbox.list=SomeArray approach takes less than a second to add ten times as many items. It’s fast.

So why would you want to add tens of thousands of items to a ListBox? And why would you want to perform a wholesale update that list by clicking in the Listbox itself? Here’s why: I’m designing a UserForm to help filter PivotTables. It’s a cross between the existing Pivot Filter functionality and a Slicer. Here’s what you see if you double-click on a PivotField header:
Initial State
Note the Search field at the top, and the three buttons immediately below that search field. If you type something into that field, then instead of displaying everything that’s currently filtered, you instead get a list of any matches, and you can then apply those search results to the underlying PivotTable via those three CommandButtons. The first cb simply filters the PivotTable to reflect the search, and the other two let you add or remove any search result from an existing filter. (The native filter lets you add, but not remove).

I want to do away with those three command buttons, and instead (in the event that a search is performed) simply list those three options at the top of the ListBox above any search results returned. Clicking on those three options will then trigger the exact same code as currently triggered by the Command Button.

Here’s how that looks currently if I actually type something in that Search box (Note I haven’t yet removed the three command buttons this new functionality will make redundant from that Search frame):
test search

I’ve added a simple bit of code to the lbResults_Change() event handler that checks if a user clicks any of those first three options. All that code does is trigger the exact same routines as would be triggered if they’d simply clicked on the actual command buttons themselves:

For i = 0 To 4
    If Me.lbResults.Selected(i) Then Exit For
Next i
Select Case i
    Case 0: cmdApplySearch_Click
    Case 1: cmdAddToFilter_Click
    Case 2: cmdSubtractFromFilter_Click
    Case 3: Me.lbResults.Selected(3) = False
End Select

The listbox gets updated just fine if I click on the command buttons, as you can see from the below. The Pivot has been filtered accordingly, and those contextual search options have been removed from the top of the listbox (and the search field cleared):
CB press
But watch what happens if I trigger the exact same code by clicking that first option in the ListBox itself:
As you can see from the above screenshot, it still shows those three options at the top, even though they are NOT in the array that I assigned to the listbox, as evidenced by the screenshot below:
The Listbox DID get updated, mind:
? .ListCount 3
? .List(0) 263213: ICT Systems Test Engineer
? .List(1) 263299: ICT Support and Test Engineers nec
? .List(2) 839313: Product Tester

…It just didn’t get redrawn. And it won’t get redrawn unless I refill the ListBox via one of those CommandButtons I’m trying to do away with. And it doesn’t seem to matter if I set the focus to the CommandButton before I try to refresh the Listbox, or even clear the ListBox entirely before I try to refresh it.

You can see this behavior for yourself in this sample file I’ve prepared.

When the userform opens, you’ll see this:
Clicking the CommandButton correctly loads a new array into the ListBox (and increases the size of the ListBox accordingly):
…but if you click in the ListBox itself – which triggers the exact same code – then while you’ll see that the ListBox got expanded, it did not get redrawn:
Just as weird: you can see that the ListBox got expanded by two lines. That’s because the ListBox_Click event got executed twice…even though I have the requisite event suppression code in place (and I’m not talking about application.enable events here, because that doesn’t work for UserForm events). Putting a breakpoint in the code also shows that the 2nd time it runs occurs immediately after the previous run finishes, and not as a response to the .list = SomeList bit that normally triggers such repeat runs.

And if I now click that CommandButton, you can see that the missing numbers from the double ListBox_Click pass are in fact there, as well as the extra addition from the latest CommandButton_Click:
If I click that Use .AddItem Approach radio button and then click in the ListBox, things go according to plan:
So there’s always that approach. But that approach sucks. Maybe I’ll be forced to keep those CommandButtons in the UserForm after all. Anyone else experience this issue, or have a workaround up their sleeve?

International Keyboard Shortcut Day 2015

It’s the first Wednesday in November and you know what that means. It’s International Keyboard Shortcut Day. The day when people from all over the world become far less efficient in an effort to be more efficient the rest of the year.

IKSD was started way back in 2014 by me. There are currently zero countries that recognize this day, but that means we have only way to go: up.

How it Works

For one hour today, you commit to using a keyboard shortcut. For the particular operation you choose, you don’t use the mouse for that whole hour. Every time you reach for the mouse to perform your chosen task, you stop, return to the keyboard, and use the keyboard to do the thing. Then give your self a chocolate or some other reward.

Let’s say the keyboard shortcut you choose is Win+E to open a Windows Explorer window (that means hold down the Windows key and press E). If you find yourself reaching for the mouse to click on the little folder icon in your task bar, you stop. Return your hands to the keyboard and press the press Win+E. Congratulate yourself and have a treat.

If you don’t catch yourself and you actually click on the folder icon, close the folder, return to the keyboard and use the shortcut. IKSD isn’t about being more efficient today, it’s about being more efficient every other day of the rest of your life.

What to Choose

There are several degrees of participation.

  1. Pick one shortcut. Maybe Ctrl+O to open a file. Maybe Ctrl+S to save a file. Maybe both. If you accidentally open a file with your mouse, close it and reopen it with the keyboard.
  2. Pick a category. I suggest Navigation as I think it reaps the greatest benefits.
    • Use Alt+Tab to switch between programs.
    • Use Ctrl+Tab to switch between workbooks (go ahead and try Ctrl+Tab in applications other than Excel).
    • Use Win+1 (or 2, 3, 4, etc) to launch programs from your taskbar.
    • Use Win+{text} where {text} is the start of the name of a program you want to launch. I use Win+file to narrow the start menu and choose FileZilla whenever I need to FTP something (see image below).
  3. Move your mouse. Place your mouse on the left side of your keyboard (lefty’s, you know what to do) and only use the mouse with your non-dominant hand. It’s excruciating, but there’s no better way to learn the value of home row.

One hour for one day. Anybody can do that.

What the Hell is Wrong with You?

Several years ago I got tendinitis in my elbow. Some say it was caused by playing golf three times a week with a pretty poor swing. But I think it was a sign from the universe. My doctor told me to move my mouse to the other side of my keyboard. I did it. It sucked. I learned so many keyboard shortcuts just so I wouldn’t have to try to manipulate my mouse with the uncoordinated meat hook on the end of my left arm. And I was converted. Now my mouse lives most of the year on the left side (I switch it back for a few months in the Winter) and my hands only leave the keys when it’s more efficient to do so. I use my mouse several hundred times a day, but only when it’s more efficient.

Enjoy IKSD and leave a comment with how you celebrated. I’ll have a couple more keyboard-oriented posts this week, because here at IKSD headquarters, the party never stops.

It’s Almost IKSD Eve

You know that this Wednesday is International Keyboard Shortcut Day, right? To start the celebration, I’m reposting a blog from with permission of the author. Enjoy!

This week (Week 26 of 2014, for the Excel aficionados), we have had a very interesting discussion on the Word-PC e-mail listbook on Word, together with many others topics that you might benefit from.

First up, how not to learn keyboard shortcuts: Don’t search for a list and try to commit it to memory. This fails, because firstly we are overloading our brains with too much information in too short a space of time (any student can verify this three weeks after an exam). Secondly, we are dealing with the list as an abstract thing, and not relating it to our use.

My own experience has been that a very simple discipline (which I will get to shortly) has worked very well for learning keyboard shortcuts.

Before that, though, it is useful to examine how we discover keyboard shortcuts. Yes, lists are useful for that—every time I see an article on keyboard shortcuts, I make certain that I read it (I just picked up a new Excel keyboard shortcut this week in that manner). Second, we all (or at least those who type as inaccurately as I do) have had the experience of pressing something on the keyboard, and seeing something go haywire (e.g. we wanted Shift+R for an uppercase R, but hit Ctrl+R and right-aligned our paragraph). Normally we just undo (Ctrl+Z), but I normally take some time out, try to see if I can figure out what I just hit (I will sometimes undo and try to recreate the keyboard shortcut with what I thought I was typing). I will also evaluate whether this keyboard shortcut is useful for me—if not (e.g., the keyboard shortcuts in Word for Danish characters are some that, no offense to the Danes, I hope I never have to use), then I just forget about it and carry on with my life. If it does appear to be useful, then I will set about learning it. Also, it pays to be observant. It amazes me that people look at menus, and never see (i.e., observe) the keyboard shortcuts listed in those menus (of course, since Office 2007, the Office keyboard shortcuts are displayed in better-hidden tooltips—yet another way Microsoft seems intent on de-cluttering by dumbing down).

One other note before I get to the learning bit. Some keyboard shortcuts are system-wide—meaning they will either work on the OS level, or will work for almost all programs known to man (if you will excuse the exaggeration). Examples are Ctrl+C for Copy, Ctrl+P for Print, Ctrl+S for Save, etc.). Some are, for want of a better term, platform-wide (e.g., Ctrl+H for Replace in all of the Microsoft Office programs, while Ctrl+R does the same in many other programs). And some are program-specific (e.g., Ctrl+Alt+M for a comment in Word, which is Shift+F2 in Excel). If I am working in a new program, I will (after having duly saved my work), freely experiment in that program with keyboard shortcuts that I believe would work there (i.e., those keyboard shortcuts that are in the first two categories I mentioned). I’ve never had a computer meltdown as a result of this (yet!), and it gives me, so to speak, more bang for my buck for the effort of learning keyboard shortcuts.

So, how do we learn keyboard shortcuts?

Still not there yet…. Some keyboard shortcuts are mnemonic (Ctrl+B for Bold, Ctrl+I for italics, Ctrl+C for Copy—and, on that point, notice the position of the X,C,V keys on a qwerty keyboard, and remember Cut, Copy, Paste—and, in Word, Ctrl+L for Left alignment, Ctrl+R for Right alignment, and Ctrl+J for Justified alignment, etc.). Unfortunately, not all keyboard shortcuts can be mnemonic (e.g., Ctrl+C is already taken, so Centre alignment in Word becomes Ctrl+E). Obviously, mnemonic keyboard shortcuts are going to be easier to remember, but we need to go further.

So, how do we learn keyboard shortcuts?

Firstly, one or two at a time. This overcomes the first mistake I noted at the start. Sometimes I will do a handful at a time (e.g., when I discovered various commonly used number formats in Excel with Ctrl+Shift+1 (Ctrl+!), Ctrl+Shift+2 (Ctrl+@), Ctrl+Shift+3 (Ctrl+#), Ctrl+Shift+4 (Ctrl+$), Ctrl+Shift+5 (Ctrl+%), Ctrl+Shift+6 (Ctrl+^)—although I never use the last one in my line of work). But when I do teach myself a whole bunch of keyboard shortcuts, there will be some relation between them, as in the list I just showed here.

Secondly, we want to relate it to usage (thus overcoming the second mistake mentioned above). Allow me to explain, because here I am finally coming to that little discipline that I hinted at. For example, this week I learned that Ctrl+6 hides all objects in an Excel worksheet. If I didn’t get that from a list (I did, in this instance: then I will make a note of it. Now I don’t work with objects in most of my sheets, so it may be two or three weeks before I get an opportunity to use that one. By then, I will have forgotten it. So three weeks from now, most people think to themselves:
“What was that keyboard shortcut for hiding objects again? Arrghh. Can’t remember it. Ah well, that just proves that this keyboard shortcut stuff is a load of junk. I’ll take my mouse, click Home | Find and Select | Selection Pane, and then click on Hide All.”

What I do, in contrast, is use this little discipline: I stop, go back to my list or note, look the keyboard shortcut up, and then use it. This process of using it activates a second memory channel (muscle engrams—go look it up), over and above the cognitive memory process. We all use this, we just don’t think about it. For example, I’m standing in front of the ATM to draw cash, and it’s one of those days where, for the life of me, I can’t remember my PIN number. So what do I do? Hopefully, not pull out the little slip of paper—that everyone can see!—where I scribbled it down. I know people who have had their accounts emptied in this way! No, I make as if I’m going to type the PIN, and suddenly it comes back to me. I am using my muscle engrams (the same things tennis or cricket or any other sport players use to perform those shots so masterfully) to bring back the memory of what my PIN is. In other words, the part of my brain that moves my hand to type my PIN also remembers that action—it is a second memory channel. So when I use that with keyboard shortcuts, I am actually memorising the keyboard shortcut in two different ways (cognitively and “physically”). In fact, even now, I sometimes struggle to recall what a certain keyboard shortcut is, but if I get behind the keyboard and make as if I’m going to do it, I can figure it out again.

Now sure, that process of stopping, looking up the keyboard shortcut, and using it, does take longer than just doing it with the mouse. But it is a short term loss for a long term gain. Once that keyboard shortcut is mastered, I will save a few seconds every time I use it, regaining and overtaking what I have lost in looking it up once or twice.

And one last thing. I know keyboard shortcuts are not for everyone. I must also confess that I am not musophbic (go look it up, although I am twisting the word beyond its original intention), and the mouse has its place—some things really cannot be done without the mouse (some programs, for example, are really not keyboard shortcut friendly) and some things (not many, but they are there) can be done faster with the mouse than the keyboard. Some things, of course, can only be done with the keyboard and mouse in conjunction (macros aside): Did you know that you can select one sentence in Word by holding the Ctrl key while clicking anywhere in that sentence? (This does sometimes trip over abbreviations, though.)

But keyboard shortcuts definitely do help you work faster, and keyboard shortcuts can help out in the most unlikely situations—those that you have never thought of (have you ever tried working with the touchpad of your laptop while seated in a plane going through some turbulence?). So much so that, in addition to keyboard shortcuts, I have memorised quite a few ribbon manipulations on the keyboard (for one example, Alt | JL | F | C to AutoFit a table in Word—I defy you to do it quicker with the mouse than I do with the keyboard. Sure, I could create a keyboard shortcut for that and do it seven split seconds faster, but typing that string is so quick, I hardly see the need.

The question then becomes twofold: So how many keyboard shortcuts do you know, punk? And, more importantly, how many keyboard shortcuts will you know a year from now?

Excel 2013 v Excel 2010 Speed Test

There are a lot of good comments on the Excel 2013 Is Unreasonably Slow post, but none of them worked for me. Even a generous offer from keepItCool, but I can’t send the problem child due to proprietary information. So I’m trying to demonstrate the problem in a simpler fashion.

Sub TestTimes()
    Dim wb As Workbook
    Dim i As Long
    Dim clsTimer As New CTimer
    On Error Resume Next
        Kill Environ$("TEMP") & "\TemplateInsert.xlsx"
    On Error GoTo 0
    clsTimer.StartCounter "Version: " & Application.Version & Space(1) & Format(Now, "yymmddhhmmss")
    Set wb = Workbooks.Add
    wb.Sheets(1).Range("A1").Value = "Insert Template"
    wb.SaveAs Environ$("TEMP") & "\TemplateInsert.xlsx"
    clsTimer.Split "Create template"
    For i = 1 To 5
        Set wb = Workbooks.Add(Environ$("TEMP") & "\TemplateInsert.xlsx")
        clsTimer.Split "Insert template " & i
    Next i
End Sub

I create a template and insert it into a new workbook five times. This is more or less what my other code is doing. Actually it’s quite a bit less and the templates in the other code are a lot fatter. But I think it demonstrates the point.

Split Excel 2013 (Home) Excel 2010 (Home) Excel 2013 (Office, Local) Excel 2013 (Office, Network)
Start 0.75 0.55 0.66 2.82
Create template 257.68 161.60 445.96 562.40
Insert template 1 449.85 246.10 739.31 927.03
Insert template 2 638.71 345.13 1,058.17 1,292.53
Insert template 3 832.92 429.95 1,367.04 1,716.61
Insert template 4 1,030.99 517.06 1,696.34 2,109.36
Insert template 5 1,247.35 611.32 2,023.30 2,467.81
End 1,247.63 611.70 2,024.43 2,523.20

Excel home is my machine. I have both versions installed. The office times are where my original code runs in production. The “Local” times are when the code is on the desktop and the “Network” time is when the code is on a server.

There’s a lot I’m not happy about here. If I could cut the 2013 times in half, I would be not-unhappy. Note also that I’m not protecting or unprotecting anything – a known change in 2013 that slows things down.

Give it a try, if you like, and let me know what your times are. The home hardware is Intel i7 860 @ 2.8Ghz with 8GB RAM / Windows 7 64 bit, Excel 32 bit.

You can download TemplateInsertTimeTest.xlsm

Conditional Formatting Icons with Relative References

This stack overflow question is intriguing. The way icon sets works is that you select a range and each cell within that range is evaluated against the other cells in that range (or a hardcoded number). The percent or value you set can be a cell reference, but not a relative cell reference. Let’s look at an example. Here are 24 numbers over two years. I want an icon in all the 2015 cells that shows how it compares to the prior year.

I set up a CF for B14 that looks at B2, but I can’t make B2 relative. It has to be absolute. Look at 7/31/2015. It’s less than 7/31/2014, but still shows an up arrow because it’s being compared to B2.

If I copy this down to the other months, the B2 remains – that’s how absolute works. If I copy B14’s formatting down to all the cells at once, I get two CF rules: one for B14 and one for B15:B25. If I copy the CF down one cell at a time, I get 12 CF rules, but they still all point to B2.

No problem. I’ll use a little OFFSET trickery. I select B14:B25 and make a rule that says

The relevant formula is =OFFSET($B$2,ROW()-14,0,1,1). You wants absolute references? I gots absolute references. No dice (I put some edge cases in there and copied the 2014 numbers down so I could see what was happening).

That should work, but it doesn’t. Instead of doing it to the whole range at once, I did that same CF to B14 only, then copied it down one cell at a time.

Et voilà! What a pain.

Editing SQL Statements in External Data Queries

Surprisingly, I’ve been using the SendKeys macro from this post quite a bit. SendKeys is dangerous, as I’ve said, but I like to live on the edge. Jan Karel commented that I should use Alt-DDE, which gives me the Command Text box to edit the SQL query, but doesn’t give me the opportunity to change the name of the Connection. As I thought about it more, changing the Connection name happens one time and isn’t really the major source of my frustration. In fact, if I were a little more disciplined I could change the name when I setup the Connection in the Friendly Name box.

Then it’s settled. I’ll use Alt-DDE to edit the SQL and I’ll force myself to set the name when I set it up. But wait. One of the things I was really looking forward to in building my own Command Text box was making it bigger by default so I could see the whole SQL string (or at least most of it). The Alt-DDE textbox is only slightly better than the Connection properties Command Text textbox. See for yourself.

That’s a crappy UI. And that’s from someone who spends a lot of time in the Visual Basic Editor.

Then it’s settled. I’ll build my own form for changing the properties I want to change. It’s what I really wanted to do anyway, so why stop lying to myself. What kind of features should I build into this UI? A big textbox is a must. Also, I’d like to be able to add white space and line breaks. Oh, and if I could have SQL parsing, autoformatting, and autocomplete… So basically what I want is SQL Server Management Studio. I already have that. It’s called SQL Server Management Studio. That lead me to my next bit of genius. If I want to edit the SQL, even only a little, I should do it in SSMS. I added a couple of buttons to the Ribbon.

The Copy button copies the SQL to the clipboard, ready for me to paste into SSMS.

Public Sub CopySql()
    Dim doClip As MSForms.DataObject
    Dim qt As QueryTable
    On Error Resume Next
        Set qt = ActiveCell.ListObject.QueryTable
    On Error GoTo 0
    If Not qt Is Nothing Then
        Set doClip = New DataObject
        doClip.SetText qt.CommandText
    End If
End Sub

I leave the button enabled and check to make sure a QueryTable exists in the procedure. If I wanted to enable/disable the button, I would need to run a SelectionChange event constantly. I didn’t test it, but it seems like too much overhead. The Paste button looks like this

Public Sub PasteSql()
    Dim doClip As MSForms.DataObject
    Dim qt As QueryTable
    Dim sOld As String
    On Error Resume Next
        Set qt = ActiveCell.ListObject.QueryTable
    On Error GoTo 0
    If Not qt Is Nothing Then
        sOld = qt.CommandText
        Set doClip = New DataObject
        qt.CommandText = doClip.GetText
        doClip.SetText sOld
    End If
End Sub

I added one little safety step in here because I know how I am. I take what’s in the clipboard and insert it into the CommandText property. But I put the previous CommandText in the Clipboard when I’m done. That way, when I get distracted and accidentally put something else in the Clipboard before I paste, I can (relatively) easily revert back to what it was.

I’ll give this a try and see how it goes.

One unsolicited plug: I use Red Gate’s SQL Prompt in SSMS. I can’t imaging having to work in SSMS without it. It’s pricey, but if you’re spending any time in SSMS, you should give it a try.

Happy Gerbitz Day

This is the first year you can sing “Happy Birthday” to Excel without having to pay a royalty, so that’s nice.

Here’s my uninteresting Excel story: My first spreadsheet program was SuperCalc. I remember we had orange screens on our PCs. Eventually we graduated to VGA monitors and Lotus 1-2-3 v.1A. I stuck with that version for a long time. I had tons of keystroke macros – whatever the heck those were called in Lotus – and I wasn’t giving them up. Excel burst onto the scene and I barely blinked. I don’t need that fancy new stuff. I used v.1A until 1992 and I think 1-2-3 was on v4 by then.

In 1992, I miraculously got a job at KPMG (nee Peat Marwick). Apple was a client of KPMG, so everyone got a Mac and used Office. It was like living in hell. I was a PC, Lotus 1-2-3 guy and I was forced to use these toys in business. Over time, I got used to my Mac Plus, then my SE30, then my PowerBook. And, of course, I got used to Excel and its obviously superior features to the version of 1-2-3 I was using. I don’t remember what version of Excel that was, I just know that most people in my office sucked at using it. Thankfully 25 years later, every office worker is, at a minimum, competent at Excel. What? That’s not true, you say? There are still people who work with Excel and aren’t competent? What the hell have they been doing for the last 25 years? It’s not like learning Excel is exactly cutting edge. End rant.

Being forced to use Office was probably a pivotal point in my life. (Using Macs in an accounting firm in the early ’90s was just stupid.) Pivotal though it was, I think what really turned me into an Excel geek was an intranet message board that KPMG had. I think they called it the KPMG Knowledgebase, but my memory isn’t so good. I could go on the message board and answer people’s questions about Excel. And I was hooked. Then it was on to newsgroups (nntp), a blog, and the crazy post-Microsoft newsgroup period that has mostly meant for me. What the heck did I do after MS closed the newsgroups but before SO? I don’t remember. I know I visited once, saw a terrible answer from a moderator, and saw that the moderator had selected his own answer as “the” answer. I haven’t been back.

When I first started on the newsgroups, I was more of an Access guy than an Excel guy. I was surely answering more Access questions than Excel at the beginning. It was when I started reading Chip Pearson, Rob Bovey, Stephen Bullen, and others posting about VBA that the tables turned. I realize that Access has VBA, but the Excel object model was, and is, a thing of a beauty. I still do plenty of Access work, but it pales in comparison to the time I spend in Excel.

Other random memories:

  • At my first MVP Summit, everyone thought I was going to be a 60-year-old guy and I was in my mid-thirties. I guess I came off as cantankerous mature in my newsgroup postings.
  • I remember after a year of DDoE, a bunch of fellow Excellers joined as authors. There were some great posts back then.
  • I remember applying for a job and taking an Excel and an Access test. I aced them both. The secretary was looking at me like I was a witch. (If you’re reading this blog, you could ace them too.)
  • I remember planning an Australian Excel conference over beers and actually going through with it. If I had a nickel for every plan I made over beers that came to fruition, I’d have a nickel.

Happy birthday Excel!

September 30, 1985 Excel was launched and I therefore wish Excel a very happy birthday and many healthy years to come!

Debra Dalgleish over at the contextures blog has gathered a nice set of stories on how people first “met” Excel.

To celebrate this great event, I’m offering a 30 percent discount on my products for 30 days.
Redeem your discount on RefTreeAnalyser and The Excel File Remediation tool by entering this coupon code: EXCEL30

So have a piece of cake with your coffee today and have one of those “those were the days” moments.

Jan Karel Pieterse