CODEWITHSUNDEEP
formsvbaexcelcontrols
Greg Lovern
Greg Lovern

Reputation: 977

Modern forms and form controls in Excel VBA

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.

Upvotes: 1

Views: 7889

Answers (3)

Tyler StandishMan
Tyler StandishMan

Reputation: 459

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.

Upvotes: 2

gokool108
gokool108

Reputation: 56

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.

Upvotes: 1

xxbbcc
xxbbcc

Reputation: 17366

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.

Upvotes: 0

Related Questions