Userform Textbox Autocomplete

I’m working on a project where the user types some stuff into a textbox. A good portion of the time, what the user will type will match one of the last few things he typed. I wanted the textbox to autocomplete if there was a match to a list. Pretty simple, I think. For purposes of this demonstration, I’m going to match to a list of random sentences in a listbox.

I had to use that old disable events in a userform trick otherwise setting the .Text property would call the change event again.

I only look at the first five characters. After that, you just have to type what you want. If there’s a match, I set the .Text property to the matching sentence and set the selection so that the user can continue typing. It all worked very nicely except for backspacing. In the above screenshot, I’ve typed He but the textbox contains the whole sentence. If I hit backspace in this situation, I delete the highlighted portion and I’m left with He. Backspace does nothing.

I was hoping to find a simple and elegant solution. Instead, I did this.

I’m using a module-level variable to determine if the backspace was pressed while in the textbox. If it was and there’s still at least one character, I simply shorten the sEntered variable by one character. That leaves the whole SelStart and SelLength mechanism working as expected.

Todo.txt TDD Part 3

As mentioned at the end of Part 2, after the creation date, the rest of the string is called the Description. It can contain projects that start with a plus sign(+) or contexts that start with an at symbol(@) or key/value pairs with a colon(:). We’ll test the projects piece now.

I’m testing zero, one, and two projects. Now let’s update Raw to make this pass

This loops through the rest of the elements of the split array and looks for a plus sign at the start. If it finds one, it creates a Project instance and adds it to the Projects collection class. The contexts will be handled similarly.

The final special case inside the description is key/value pairs.

Again I’m testing zero, one, and two instances.

Everything else is the description

Here are the changes to the bottom of Raw

And that’s it. A properly parsed Todo.txt string ready to be used in your application. And if I make an changes to my app, I can run these tests to make sure I didn’t break anything.

You can download TodoTxt.zip

Series:
Series:

  1. todo-txt-tdd-part-1/
  2. todo-txt-tdd-part-2/
  3. todo-txt-tdd-part-3/

Todo.txt TDD Part 2

In Part I, I started writing tests and then writing code to make them pass. Let’s continue with more tests.

The next test will be for an incomplete todo with no priority and a completion date.

Hey, it already passes. Let’s add some tests for when there’s a priority and a completion date

I expected these would already pass as a result of my refactoring, and they did. The next part of the spec says “Optional Creation Date, must be specified if completion date is”. First, I just want to test that it exists. That is, if there’s a completion date, there must be a creation date.

This fails because I haven’t parsed the creation date yet. So let’s do that.

My test passes, but I broke a previous one. In my prior completion date testing, I didn’t include a creation date because I wasn’t that far in the spec yet. I need to rewrite those tests

I added a creation date to the Raw for each of those tests, and now all tests pass. Now I can move on to testing what the creation date actually is.

This test already passes. Once I get past the creation date, the rest of the string is called the Description. It can contain projects that start with a plus sign(+) or contexts that start with an at symbol(@) or key/value pairs with a colon(:). We’ll test those in the next part.

You can download TodoTxt.zip

Series:
Series:

  1. todo-txt-tdd-part-1/
  2. todo-txt-tdd-part-2/
  3. todo-txt-tdd-part-3/

Todo.txt TDD Part 1

Earlier, I wrote a post inviting you to try your hand at test-first development. This post is the first in a series of how I did it. In the previous post, I had all the tests written, but here I’m starting from scratch and writing the tests as I go. Well, I’m not starting from scratch in that the classes are already set up. If you want to see what the classes look like, download the workbook from the previous post or the one at the bottom of this post.

First, create the property in CTodo that will parse the string. There’s nothing in it, but we’ll get to that shortly.

Write a test. This test will determine if the todo item is complete. Per the spec, the first thing in the string is an “x” if it’s complete

Now write the simplest code to make the test pass. I probably could have written simpler code than this, but don’t get too hung up on that. Just write simple code and don’t try to solve the next test – only this test.

When I split the string on a space, the Complete property is set to whether the first element is “x”. The test runs successfully. Next, write a test for incomplete todos.

Oh goodness, that test already runs successfully. There’s no “x”, so Complete is set to False. Next, write a test for a completed todo with a priority. Per the spec, the first element after the optional “x” is a capital letter in parentheses.

This test fails on Debug.Assert clsTodo.Priority = "A", so it’s time to write the simplest code to make it pass.

The Priority property is set to the second character of the second element. The test passes. Did we break anything? Let’s see.

Nope, everything passes so far. Time for the next test. Check the priority for an incomplete todo.

It fails, so let’s write some code

If my fist element is an “x”, get the second element, otherwise get the first element. Pretty simple and the test passes. Every test I write, I add to the TEST_All() procedure to make sure I don’t break any prior tests. The next part of the spec is an optional completion date. Let’s start with a completed todo with no priority and a completion date.

My new test passes, but I get an error in one of my old ones. Plus this code is getting pretty ugly. When your code is ugly or repetitive, it’s time to refactor. Instead of a bunch of nested If’s, I’ll just move a pointer down the line.

I use lNext to keep track of where I am in the array. If the first element is an “x”, I advance the pointer. Then I check vaSplit(lNext) rather than a specific element number. All my tests pass.

In the next installment, I keep writing tests, writing code, and refactoring.

The below workbook has all the tests and the completed Raw property. It also has a userform, but it’s not complete.

You can download TodoTxt.zip

Series:

  1. todo-txt-tdd-part-1/
  2. todo-txt-tdd-part-2/
  3. todo-txt-tdd-part-3/

Test First Todo.txt

In my ongoing struggle to find a todo list app I like, I took a look at Todo.txt. I ended up going back to GoodTodo, but I was intrigued by the text based system. I wanted to build something in Excel to be an interface to Todo.txt and I used a test-first methodology to parse the file. You may remember my first foray into test-first development when I converted numbers into words.

Later this week, I’ll post how I wrote the parser using tests. If you’ve ever wanted to try to write code using test-first, here’s your chance. Download the workbook below. I’ve setup all the classes and the tests. I even wrote enough code to make the first test pass. If you’re interested in this exercise, follow these steps:

  1. Download, unzip, and open the workbook
  2. Go to the MTest module and run the TEST_All sub and see that it passes
  3. Uncomment each link in TEST_All one at a time
  4. Run TEST_All to see that it doesn’t pass. If it passes, uncomment the next test
  5. If it doesn’t pass, go to the Raw property in the CTodo module and write just enough code to get the test to pass
  6. When your code looks cumbersome or you see a pattern emerge, refactor Raw
  7. Repeat until all the lines in TEST_All are uncommented

Check back later this week to see what I came up with.

You can download TodoTxtTestFirst.zip

Seven steps to (almost) protected Calculated Columns

Earlier I complained about how you can’t lock down Calculated Columns in Tables, while still allowing users to insert new rows. Dick gave me half a hack in the comments of that post, and I’ve come up with the other half to give you an almost-foolproof way to protect those precious Calculated Columns from your almost fools.

Step 1: Unprotect the entire rows that your Table occupies.

Step 2: Park a shape over the Calculated Column. Leave it’s protection as the default ‘Locked’. This will act as a shield once the sheet is protected.

Step 3: Format the shape so that it has no border, and a white fill that is 99% transparent. (If it’s 100% transparent, then Excel will let users click right through it, to the cells behind).

Step 4: Add some DV that stops users from changing the formula in the column, should they navigate around the shape shield via using the arrow keys. Dick’s formula below ensures that someone will have to spend an awful long time typing before they are allowed to overwrite the existing formula.

Step 5: Add a pop-up input message that politely warns them not to mess with your calculated column, should they manage to reach it using the arrow keys. This is still needed, because while they can’t change the formula, they can still clear it using the Delete key.

Step 6: Protect your worksheet in a manner that allows row insertions/deletions.

Step 7: Go vote for Zach Barresse’s UserVoice request for Microsoft to fix Tables so that you don’t have to use 6 hacks to work around their “feature”.

This works a treat: Try as they might, they can’t select the unprotected cells in the Calculated Column by clicking on them. If they click in the bordering cells and use the arrow keys to navigate behind the shield, they get warned off politely:


…and yet they can still insert new rows:

…and as you can see, the unprotected formula in the calculated column copies down just fine.

Stack Overflow Milestone

Look how special I am.

This is it. From what I can tell, 25,000 is the last milestone that can be achieved. So I’m at the top of the mountain.

I haven’t had as much time to answer questions on SO lately, but I still find answers on there all the time. I love the way the site is organized and the outstanding UI. I wish I could get the comments box on this site to behave just like their answer boxes. And by “wish”, I mean I want it to be that way without any effort on my part.

I did happen to answer a question the other day about the “Loop without Do” compiler error. As usual, properly indenting your code avoids these errors. I guess people didn’t like the question as it got a few downvotes. I’m not so critical. I think people tend to forget their early struggles as they become more proficient with something. I remember when I first starting learning Ruby, I didn’t know enough to formulate a non-stupid question. It’s not knowing what you don’t know that can be the most frustrating. And a little throw away tip like indenting can be really helpful.

I’m really thankful there are sites like SO (and NNTP back in the day) that give me an avenue to help people. I get so much more out of it than I give that I feel guilty (not really).