22 thoughts on “UserForm Controls”

1. I’ve used a LOT of 3rd-party ActiveX controls in past VBA programs with Excel, including non-Microsoft ones:
   – Oracle Objects for OLE (http://www.oracle.com/technology/tech/windows/ole/index.html)
   – RefEdit Control
   – Windows Common Controls (mscomctl.ocx)

   Lots more I can’t think of now.

   I’ve had to use features to access Oracle database, populating tree controls… I even created my own ActiveX controls in another language, using those in my Excel macros.

   The only problems I’ve had is trying to use methods or properties from those contrnols that Excel doesn’t easily support; I’ve had to declare functions from the OS’s DLLs (eg. Public Declare Function SendMessageLong Lib “USER32?…)

2. I used the Microsoft Office Spreadsheet control to display the results of an ODBC query in a userform. It also had the added benefit of exposing an object model similar to Excel’s, so I could reuse my code in automating it. It worked great on my machine at work with Office XP. It also worked great on my home machine with Office 2000. When I was comfortable enough to deploy the app to my boss’s boss, it did not work at all. I had problems with the object not being found, which turned out to have to do with the version of the control used. I attempted to use an earlier version and got past that error only to run into another error due to the fact that it was a Windows 2000 box. I could not figure out a workaround, nor could I be sure that even if it did work that it would work on the other 50 something machines it had to be installed upon so I gave up.

   For my own personal use, I have gotten great utility out of the web browser control. Also, the progress bar control is good if you want to make a Microsoft-ish progress bar, though I have a cusotmized version of the one from your book that looks more polished.

   One time I used the spreadsheet control directly on the worksheet itself. It took a while to get it just right, but I eventually got it so the control was overlaid right where the spreadsheet normally was. Then I hid the controls on the worksheet and made it so you couldn’t tell that it was not a regular spreadsheet at all. I then put a macro on workbook open to populate the spreadsheet with a couple of hundred thousand rows of data that were stored in multiple hidden worksheets in the workbook. It looked just like an Excel book that magically broke the 65,536 barrier. I had my coworkers almost convinced.

3. John,

   The best solution is to use thirdpart’s Active X controls with COM Add-ins.
   In general I use ComponentOne’s controls, IoComp’s controls, Chart FX, MS OWC and SamLogic’s controls.

   Kind regards,
   Dennis

4. I’m currently using the Microsoft Calendar Control v 9.0 and VB 6 DataGrid control in an application running on Excel 2000 & 2003, with no problems. The only issue with the DataGrid control is some restrictions on systems without VB installed because of the lack of a design environment license; e.g., can’t add it to a new userform. Works fine at run-time, though.

   Regards,
   Mike

5. I have also used the dtpicker control. It works on the three pc’s that I’ve tested on so far. While we’re on the topic of non-standard userform stuff, is there any way of using “decorations” on the userform? By decoration I mean using lines, borders and other non-functional items that help visually separate controls on the form.

   Thanks,

   Charles

6. The DTPicker is a great tool because it has a professional look and feel and avoids the need for complicated validation of the user’s input. I also had problems with the DTPicker when the userform I created wouldn’t open on the client’s machine but I managed to find this solution …

   http://www.ascentive.com/support/new/support_dll.phtml?dllname=MSCOMCT2.OCX

7. I suppose you could create lines by adding a label with a solid background, then changing the height to a 1 or 2 for a horizontal line or the width for a vertical. Or you could create a graphic in an imaging program and add it as an image control. This could look pretty good if you make it a gif with a transparent background and set the image control background to translucent and the borders to none.

   I don’t have VB6, but I wonder if you could import its drawing controls if you have it installed?

8. I’ve become a fan of the treeview control, Works a treat.

   I used it in Name Manager to show where a name is in use.

9. I’ve used a few, I dont like installing them, when I’m not at the PC, but is can be done with shell reg fuction I think. I would rather use “standard” control and work with them as much as possible. I think Windows Common Controls can be classed as standard as I dont think I’ve ever come across a PC with out it on (Death roll I’m sure!). I tend to use 3rd party Grid controls a bit. There are some very good free active-x controls out there.

   Die_Another_Day:

   Try using frames to split things out, not putting controls in the frame, but useing a real thin frame as a bar like feature. Also pasting pictures in to a image control can be useful to.

10. I use standard VBA controls almost exclusively (and I’m including REFEdit in this group). I also use Windows common controls from time to time. I have had mixed results with the calendar control, and a horrific time with a grid control. So I keep it simple stupid.

11. Thanks for all of the comments. I’m with Jon. KISS.

    I’ve been thinking of using some non-standard controls in my PUP add-in, but I really don’t want to complicate matters. At this point, I estimate that less than 1% of my users have any type of installation problem — and that’s almost always because they haven’t followed the instructions. I think that percentage would go up considerably if I messed around with other controls. But the main problem is that I wouldn’t know how to instruct them how to fix it.

12. I’ve found the DTPicker control is particularly useful, as its analygous to calendar type controls found on many websites etc – all but the most technology challenged seem to manage with it. On the down-side is the trouble one encounters when trying to enable it on other machines… but it looks like Primo might be onto something…

13. John: No problems reported yet with Name Manager (which as I said uses the treeview). I guess the ones from the common control lib are relatively safe.

14. Hi,
    In Excel – Add ins screen there is an option of “Automation”. I could never make out What is it all about ?

    Regards
    Kanwaljit

15. As long as Windows is shipped with the collection of Windows Common Controls then it’s OK to use them. Will the final version of Vista include the collection?

    Kanwaljit:
    ‘Automation’ refer to Automation add-ins. This kind of add-ins include User Defined Functions and are created with other development tools then with VBA.

    The following KB-article gives more insight about them:
    INFO: Excel COM Add-ins and Automation Add-ins
    http://support.microsoft.com/default.aspx?scid=kb;en-us;291392

    Kind regards,
    Dennis

16. I agree on the KISS suff.
    I have had problems with the common controls (rich text box) due to operating system differences. In distributed tools, as soon as you get out of ‘pure’ Excel/VBA you need to consider installation which is a whole other project.

    The Automation option is from xp onwards, it allows you to add user defined functions written in COM type add-ins in VB6/.net etc. These can then be used directly in cells, in 2k and before you needed a VBA wrapper.

    Cheers
    Simon

17. I have used some of these controls successfully but they always leave me with that impending sense of doom. The one thing I make sure to do is to leave them as “stock” as possible. That includes changing colours or formats or whatever else they can do. I figure that they are probably reasonably well tested in their default configurations. If you start gettin fancy then… BOOM…

18. Way back in the days of Office ’97 and the struggle with SR-1 and SR-2 releases you’d never use a control out of comctl32. Version 5.0 or 6.0? Either way, it never worked on half the user boxes.

    That was then and this is now: I have no trouble with Calendars, ListView, TreeView and ImageList controls in Excel 2002, even though Systems are no better at maintaining a uniform desktop ‘build’, nearly a decade later.

    N

19. Hello.

    I’ve been using Microsoft Chart 11.0 in an userform. I thought it worked just fine, but then someone tryed it in a different computer and it didn’t work…

    “Object could not be loaded”…

    Does anyone know of a way to solve this? Should I use a previous version of Microsoft Chart?

    Thanks.

    J

20. Does anyone know how to add DTPicker on the fly in VBA code like John Walkenbach does in his data form with combo and text boxes? The issue seems to be how to refer to it in the controls.add(something)line.

21. HI,

    I prepared the user form with DTPicker, so far on my PC its working fine. i sent this form to install in another machine. he couldn’t able to install. he got the error “could not load an object because it is not avaiable on this machine. then i checked his registry key and mscomct2.ocx everything in that machine. i did change key but still i got the same error

    any one please if you have got idea

    thanks
    rana

22. Hi rana

    I think it’s like this: When you put something onto a userform then the user is most often, at least in theory, able to modify the form and the code. Meaning that the user needs a “developer license” to use the control. When used in a compiled app, an exe or dll, then a runtime installation is sufficient; properly registered in windows/system32 and that’s it.

    So I agree with John, Jon & others; Don’t. Write your own dtpicker using the standard Forms2 controls and nothing else. DLL hell exists.

    Best wishes Harald

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.