Category Archives: Microsoft Office

Unwanted Worksheet Events? Maybe SpecialCells() Is To Blame!

I’ve written a lot on this blog about Range.SpecialCells() and how careful you have to be when using it. Here’s yet another caveat which I didn’t know about until another developer brought it to my attention. Put some data in a worksheet … Continue reading

Posted in Microsoft Excel | Tagged , , , , , , , , , , , | 3 Comments

#REF! Means Your INDIRECT() Formula May Be Slow As A Dog

A user recently handed me a spreadsheet and asked me to investigate some bizarre behaviour. For some people the spreadsheet’s calculation was fast, for others quite slow and, for a few, extremely slow. After a bit of playing around I found what … Continue reading

Posted in Microsoft Excel | Tagged , , | 5 Comments

VBA: Understanding ByVal In An ActiveX Textbox KeyPress Event Handler

I was posting on one of the online forums a while back and helped a user understand some code which he had found on the web and, in particular, how it restricted what could be typed into a textbox on … Continue reading

Posted in Microsoft Excel | Tagged , , , , , | Leave a comment

Minimize And Maximize Userform Across 32-bit and 64-bit Environments

If you scour the internet you’ll find plenty of examples of how to give your VBA userform minimize and maximize buttons. The exact code will vary depending on the trinkets the programmer wants, but a typical snippet will look something … Continue reading

Posted in Microsoft Excel | Tagged , , , , , , , , , , , , , , | 1 Comment

Stop MSForms.Frame Scrollbars From Resetting On Focus

I’ve been doing some more work on my dynamic grid control – which mostly comprises of MSForms Comboboxes in a Frame. A feature of a Frame control with scrollbars is that when it gets the focus the scrollbars’ positions reset themselves. Here’s some quick … Continue reading

Posted in Microsoft Excel | Tagged , , , | 4 Comments

Deceptive Appearances: MSForms Textboxes And Comboboxes

The Microsoft Forms 2.0 Object Library (FM20.dll) really lacks a number of controls. One which I seem to miss the most often is a “grid” control which will let users select rows and edit data in multiple columns. For this sort of … Continue reading

Posted in Microsoft Excel | Tagged , , , , , , , , , , , | 2 Comments

Error Accessing The System Registry

As the resident Excel expert at work, I have the privilege of being landed with some weird and wonderful errors which have to be fixed. Last week I developed an Excel spreadsheet for a user but when he went to test it he … Continue reading

Posted in Microsoft Excel | Tagged , , , | 2 Comments

Naughty Sorts

Continuing the theme of naughty code which can cause Excel XML corruption, let’s have a look at some code which sorts some data in a range but then causes XML errors when the file is saved and re-opened. Here’s my sheet: … Continue reading

Posted in Microsoft Excel | Tagged , , , | Leave a comment