1

The look of Excel VBA's forms and controls hasn't been updated since Excel 97. They look like Windows 95. It's become a very old-fashioned look.

Some of the controls, such as checkbox, radio button, and edit box, have properties that can make them look two-dimensional. Back in 1997 that made them look old-fashioned like Windows 3x, but today ironically(?*) it makes those controls look more modern. I've been doing that, but it doesn't work well for some of the other controls, especially command buttons and dropdowns.

Is it possible to call the Windows API directly to get more modern-looking forms and form controls? If so, has anyone made some boilerplate VBA modules for them?

Also, can it be done for Excel on Macintosh too?

*I used to think I understood irony, but I'm never sure anymore.

Greg Lovern
  • 958
  • 4
  • 18
  • 36

3 Answers3

2

This isn't necessarily getting access to additional controls, but I found this incredibly useful for controlling things that VBA doesn't provide access to (sadly). You can use the class provided here: http://www.oaltd.co.uk/Spreads/FormFun.zip to access some Windows API functionality on the form.

Assuming the class is called cWindowsAPI:

private sub UserForm_Initialize()

    Dim formVar as cWindowAPI
    Set formVar = new cWindowAPI

    Set formVar.Form = Me

    '    Now you have access to the different options available via the api
    '    that were exposed within the cWindowAPI class of course
    formVar.ShowCaption = false
    formVar.ShowMaximizeBtn = false

    '    Etc...

End Sub

Perhaps an expansion could be made upon this with useful APIs that you come across.

airstrike
  • 2,270
  • 1
  • 25
  • 26
Tyler StandishMan
  • 449
  • 3
  • 10
1

Perhaps build a Dictator application and use Excel shapes as front end controls. Will be nice and glossy with 3D effects. But this also takes alot of coding in my experience, as each of state of each shape object has to be coded by yourself.

gokool108
  • 56
  • 3
  • Although on the bright side while typing this post, I just created a toggle/ Radio Button replacement - Grey to Green with glow, using a Oval shape in Excel. It only took me 4-5 mins, so once you get the hang of it, it is do-able – gokool108 May 29 '15 at 07:29
0

Calling the Windows API from VBA is possible but it's a lot of work, error prone and fairly hard to debug - I wouldn't recommend it.

Alternatively, you can create ActiveX controls using C# or VB.Net and then use those controls in your project - this is a lot simpler to do. This question / answer have more details.

Community
  • 1
  • 1
xxbbcc
  • 16,930
  • 5
  • 50
  • 83
  • That would require distributing a DLL that every user would have to install. Few clients want to do that, and where my client is going to distribute the automated Excel workbook to his clients, each of whom are in their own corporate environment with its own rules and restrictions, needing to distribute a DLL is usually a deal-breaker. I've called Windows APIs on many projects. Do you mean you find it hard in general, or just hard to do it to create forms controls, which I would guess is a complex project? – Greg Lovern Sep 12 '14 at 02:13
  • 1
    @GregLovern Well, you didn't mention that in your question. Plus it's not like it changes things much - you _can_ call API functions from VBA but it's a lot of work to get even simple controls working. – xxbbcc Sep 12 '14 at 02:14
  • @GregLovern Implementing full custom controls through API calls from VBA is a very complex project - one would have to redeclare all relevant the types / API function declarations from windows.h and then write the code using those. That's a huge amount of work to begin with and is very error prone. There are other issues: I'm not sure you can easily get a window handle to the spredsheet, etc. – xxbbcc Sep 12 '14 at 02:31
  • 1
    Getting window handles to various Excel elements is not difficult; I've done that for a few Excel VBA projects. – Greg Lovern Sep 12 '14 at 03:04
  • @Rory That is correct. I went with the assumption that the OP knew that, seeing how he already worked with low level window handles. – xxbbcc Sep 12 '14 at 14:48