Daily Dose of Excel

David wants to know why the WMP control won't start playing right away. I'll be damned if I know. Maybe you can tell us. First, show the Control Toolbox and click on More Controls. Find the Windows Media Player control and put it on Sheet1. Next, put these subs in a standard module:

Change the URL line to point to a file on your computer. If you run the first sub, the WMP control won't start playing for five seconds. Oddly, it starts playing before you dismiss the message box. So it's not that it's waiting until the end of the sub.

The second sub, just_play, starts playing immediately. It doesn't wait for Application.Wait.

What the heck is going on here?

Yesterday I installed OpenOffice on a couple of laptops. In order to accept the license agreement, I had to scroll down to the bottom of the textbox. The "Accept" button was disabled until I'd reached the bottom. I don't know why they care if I read their EULA. I could go on and on about how EULAs are too complicated and probably unenforceable when you really need them, but I won't.

Instead, I was interested in replicating the form in VBA. (Perhaps a violation of the EULA? How delicious.) I started with a Textbox control on a userform. I set Multiline to TRUE, WordWrap to TRUE and ScrollBars to fmScrollBarsVertical. It looked like the OpenOffice form, but I couldn't figure out a way to tell where the scroll bar was in the Textbox. That is, I didn't know when the scrollbar was all the way to the bottom.

Next I went to the Listbox. I knew Listboxes had a TopIndex property that shows the top-most item that's visible. With the size of my Listbox and 100 lines of an EULA, I hardcoded that when TopIndex is 78, the user has scrolled all the way down.

Knowing that TopIndex is 78 and doing something about it are two different things. I discovered, or rediscovered, that clicking on a scroll bar does not fire the Click event of a Listbox. Nor does it fire the MouseUp event. Apparently, the scrollbar of a control is in the domain of the userform rather than the control. I settled on the MouseMove event of the Userform object. It's not perfect, but it's close.

One nice thing is that I don't have to manage the Enabled state of the commandbutton. Once the user hits the bottom, it's enabled and that's that. Scrolling back up doesn't negate the fact the user was at the bottom and presumably read the agreement (yeah, right!). One obvious downside is that Listboxes aren't really meant to hold text. Editing the text would be a huge chore because one wayward line break affects all that come after it.

I would be nice if '78' wasn't hard coded. I'm sure I could figure out the appropriate TopIndex based on the Height and ListCount properties. I also may need to know the Font property. As you can see, my interest dried up before I got to that point.

As usual, your improvement are welcome and appreciated.

The TakeFocusOnClick and Default properties are not behaving like I'd like them to. ActiveX command buttons (the ones you put on userforms) have these two properties. Setting TakeFocusOnClick to False (the default is True) keeps the focus on whatever control had it before the command button was clicked. Setting Default to True causes the Click event of a command button to run when the Enter key is pressed regardless of which control has the focus.

A typical use of the Default property is for an OK button on a form. I use this when I want the user to be able to dismiss the form and apply the changes by pressing Enter. A similar property, Cancel, does the same thing with the ESC key. Typically a Cancel button has its Cancel property set to True so the user can get out of the form by pressing ESC regardless of where he is in the form.

I have a ListView control on a userform. When a user double clicks on a ListItem, a different userform pops up. It seems to me that selecting a ListItem and pressing enter should give the same result as double clicking. I base that on the fact that I can press Enter in a file open dialog box and it has the same effect as double clicking. File open dialogs are just ListViews, after all. As I was pondering the file open metaphor, I thought that maybe the Open button is simply the default button and that's why Enter would work the way it does.

That seemed like a reasonable solution, so I but a command button on the form and set its Default property to True. The user would expect to still be in the ListView when the secondary userform was dismissed, so I also set the TakeFocusOnClick property to False. It works great. I click the button, the userform appears. I dismiss the userform and I'm still in the ListView.

You've probably guessed the problem by now. When I press the Enter button, the useform appears as expected. But when I dismiss the userform, the command button has the focus rather than the ListView. It seems that firing the Click event via an actual mouse click respects the TakeFocusOnClick property, but firing via the Default property doesn't.

In a previous post, I used the column header to sort the ListView. Now I've abandoned sorting and I'm using column headers for something else.

I need a way to easily add the same data to all of the ListItems. My ListItems are products and each product has an associated Board Software Package (BSP). The vast majority of the time, the BSP will be the same for every product on the order. There are situations where this is not true, but mostly it is. I need to add "v14.50" to each BSP column of the ListView to indicate the version that each product gets.

My original approach was to make the ListView multiselect. I envisioned that the user would select all the items and even added a "Select All" button to facilitate that. The textboxes used to edit the data normally would show the information for the selected item. But if more than one item is selected, I would have to do something different. My thought was that I would loop through all of the selected items and only display the information if it was the same for each item. If two items are selected and they both have "v14.50" in the BSP column, then I would show "v14.50" in the BSP textbox. If, however, one item has "v14.50" and the other has "v14.30", I would show "Multiple" in the textbox.

I began coding that piece and immediately I didn't like it. The ListView's ItemCheck event passes an Item variable - only one Item, not an array of all the checked Items. That means the event will fire once for every Item that is checked and the event would loop through each Item in the ListView. It seemed very cumbersome, so I started looking for a different approach.

I feel like I'm trading ease of use for ease of coding. At some point in my coding career, I established (in my own mind) a correlation between how easy something is to code and how easy it is to use. I suspect I did this because I love nice, clean code. And probably because I'm lazy.

My final approach was to use the ColumnClick event. First, I changed the Multiselect property to False. This made coding the textboxes much easier.

Don't mind all the 2's and Select Cases. I have ListViews on each page of a Multipage control and I want to use the same procedures to fill the textboxes. This approach allows me to hold the currently selected Item in a module-level variable which makes it much easier to write back to the ListView when something in a textbox changes.

To handle multiple row updates, I use the ColumnClick event. The first two columns aren't allowed to be changed, so I start my Select Case with column 3. The Select Case sets a Textbox variable and the loop writes that textbox's Text property to each row.

I've definitely sacrificed some usability here. The first thing I sacrificed is intuitiveness. While most people are familiar with multiselect, particularly when there are checkboxes next to each item, nobody without proper training is going to know to click on the column header. Not that the multiselect route was perfect. I had a hard time envisioning how that was going to work. For instance, I could see someone accidentally filling "Multiple" into every Item, which would be bad.

The other sacrifice my users unknowingly made is the ability to update multiple rows while leaving others unchanged. The ColumnClick approach updates all items, which is less usable than being able to update, say, half of them. Updating all items will be the proper course in about 90% of the cases, so I don't feel too bad about it.

Then there's the abandonment of the sort feature, which I won't miss at all.

I made a few other changes to the ListView in the process. I set the LabelEdit property to lvwManual. I have no idea what lvwManual is supposed to represent, but it means the user can't edit the Item inside the ListView. I was not able to set FullRowSelect to True and have it persist, so I had to set it in code in the Initialize procedure.

Does anyone use non-standard UserForm controls? By non-standard, I mean those that normally don't appear in the VB Toolbox.

I generally avoid using such controls because I'm afraid that they won't work reliably for all users. Today I was playing around with the Microsoft Date and Time Picker 6.0. I don't even know where it came from. Is it installed with Windows? In any case, I had a few problems. Specifically, occasional "object could not be found" errors. Restarting Excel solved the problem. I was using Excel 2007 beta, so that could explain it.

I'm interested to hear about your success (or failure) using 3rd-party ActiveX controls on UserForms.

Chart control - Using the Spreadsheet control as data source

Speaking about the Spreadsheet control, I received an e-mail from an alias (?) asking how to customize the column- and rowheadings. Below is a snipped code that shows how to do it:

The below image shows how the control looks like after executing the code:

In the following example it’s assumed that the Spreadsheet control is populated with a Recordset containing quarterly data for two countries. In addition it’s a request to show the maximum / minimum amount of freight values as well as the number of shipments.

The following image show how the form is set up in the first place:

The formulas have been created at design time and the formulas are the following:

Max: =MAX(C2:C9)
Min: =MIN(C2:C9)

When clicking on the “View chart” – button the form expand and show the Chart control as the following image shows:

Whenever the underlying data is updated in the Spreadsheet control it’s automatically reflected in the Chart control.

The following code does all the hard work to set up the Chart space and the two charts:

To sum up
For the last three years I have shipped over 20 customs solution that, in one or another way, include one or more of the controls in the OWC. I’ve mainly used them in COM add-ins for Excel or as part of VB 6.0-solutions but due to NDAs I’m not able to show any of these solutions in public. Anyway, up to this date no issues have been reported to me due to the use of these controls and therefore I find them to be trusted.

This post ends the introduction of the OWC package and although I have only skimmed on the surface I hope that you at least now have an idea about them.

Kind regards,
Dennis