Yesterday, I installed a host of updates, including some of Office.
As it happens, I tried to add an ActiveX control to a worksheet and received an error.
After some research I discovered the cause of the error to be two-fold:
1. The controls were updated by the update
2. Excel did not clean up after itself properly and left some temporary files behind.
The solution is to:
– Quit Excel
– Open Explorer
– Select C: drive
– Search for *.exd
– Remove all files found.
Hope this helps other people who might be suffering from the same problem.
Jan Karel Pieterse
#####UPDATE Dec 22, 2014#####
Microsoft has published a so-called Fixit to make resolving this matter easier:
Some time ago I announced I was working on “An MSForms Treeview” replacing the Common Controls Treeview with an all-VBA counterpart.
This home-made treeview control will work on any Office version as of Office 2000, including 32 and 64 bit Office. I expect it will even work on MAC Office, but I’m still waiting for test results.
Peter Thornton (thank you Peter!) jumped in on the project enthusiastically and really made a difference adding all sorts of usefull stuff and optimising the code for performance.
Now we’re ready for beta testing.
Please visit this page of my website for a description of the control and a download file which includes a demo userform implementing the treeview classes we built:
An MSForms (all VBA) treeview
Tell us what you think of it (oh, and please report bugs too!).
Jan Karel Pieterse
If you have ever used the Treeview control from the “Additional controls” section, then you know what a versatile control this is to show hierarchically organized data.
There are a couple of problems with this Treeview control:
- Compile errors due to a difference in how the control libraries are registered in 32 bits Windows’ System32 and 64 bit Windows’ SysWOW32 folders. If you distribute a file that was saved in 64 bit Windows, containing one of the “Microsoft Windows Common Controls 6.0” (The Treeview control is one of them) and with the reference set to “mscomctl.ocx”, people using 32 bit Windows will almost certainly have problems. At best it could entail removing both the control and the reference and replacing both, but at worst the user’s Excel can crash when trying to load the file and run the app.
- The standard Treeview control, like all non built-in ActiveX controls, cannot be used in 64 bit versions of Office.
Especially the second point convinced me it is time to develop a custom-made Treeview “control”, that only uses the native Office forms controls. I started building this a couple of weeks ago and after some time I tricked Peter Thornton into helping me with it :-)
The screenshot below shows both our new Treeview (left) and the Windows one (right) side-by-side:
Not bad, right?
Both Treeviews allow for checkboxes:
And both allow icons (windows Treeview not shown here):
You can also edit a node:
And expand and collapse nodes and navigate the tree using your arrow keys.
We built the custom Treeview using just two class modules. Using it in your project will require nothing more than copying the two classes and adding a bit of plumbing to your userform: some code and an empty frame which will hold the Treeview and possibly a frame with pictures for the icons.
We’re currently doing some cleaning up (like removing obsolete debugging stuff, adding comments and the like), so the “control” is not quite ready to be released to the outside world. Keep an eye on this blog, once we’re done we’ll post here.
Well, what do you think, is this useful or what? What functionality would be critical for you? Let us know!
Jan Karel Pieterse