How to create "modern" controls on a worksheet

[Josh Sale]

I have an add-in that dynamically creates various kinds of OLE controls on a
worksheet. I use code like the following:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", ...
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", ...

I need to use OLE objects rather than say Form controls (which at least in
the case of combo boxes look a little better) because I need the events
exposed by the OLE objects.

Anyway, all of this works fine and has been stabile for years. Buy my users
increasingly complain about the aesthetics of these controls. Without being
to precise, I think the above code results in Windows 3.1 controls being
created (OK, maybe its Win95) and they just look like old clunkers.

I'm looking for a way to create contemporary WinXP (or perhaps even Vista)
controls on my worksheets. I need command buttons, combo-boxes, text-boxes,
list-box, option button and check-box controls. Installing a new dll or ocx
onto my user's systems to make this happen would be OK.

Anybody got any bright ideas?

TIA,

josh

 

[Jim Cone]

The command button control (and others) have a picture property.
You can get free Vista pictures off the internet.
Try some of those on your controls. You can link the pictures
to the controls or simply paste the pictures into the picture property.

Are you sure it isn't the developer that cares about "modern" controls
and not the users? <g>
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Josh Sale" <jsale@tril dot cod>
wrote in message
I have an add-in that dynamically creates various kinds of OLE controls on a
worksheet. I use code like the following:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", ...
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", ...

I need to use OLE objects rather than say Form controls (which at least in
the case of combo boxes look a little better) because I need the events
exposed by the OLE objects.

Anyway, all of this works fine and has been stabile for years. Buy my users
increasingly complain about the aesthetics of these controls. Without being
to precise, I think the above code results in Windows 3.1 controls being
created (OK, maybe its Win95) and they just look like old clunkers.

I'm looking for a way to create contemporary WinXP (or perhaps even Vista)
controls on my worksheets. I need command buttons, combo-boxes, text-boxes,
list-box, option button and check-box controls. Installing a new dll or ocx
onto my user's systems to make this happen would be OK.

Anybody got any bright ideas?
TIA,
josh

 

[Josh Sale]

Starting with your last point ... I could care less. However I've been
hearing this drumbeat for years and have been ignoring it thinking "there
must be more important things to worry about then these cosmetics". But the
beat goes on and in fact intensifies. If you remember the old "Lucky"
commercials well then I'm the opposite ... I'd rather switch than fight!

As you point out, command buttons do have a picture property. But that
really doesn't do it for me. Here are two immediate problems:

First, I need to be able to dynamically set the caption of the buttons. You
can't do that if the button face is coming from a graphic image.

Second setting the picture property doesn't really address the user's
complaints. They want "modern" controls. So for example, when you roll the
mouse over a modern command button, it gets a "highlight". This doesn't
happen when you set the object's Picture property. OK ... perhaps there is
a mouse_over event I can trap and load a new picture into each command
button as the mouse rolls over it (although I actually think there may be no
such event), this is way overboard. I shouldn't have to try to replicate
all of the "modern" behaviors of these controls in my code. Its a waste of
my time, I'll never get it 100% right and I'll have to make a career of it
(e.g., next Vista and then whatever follows that).

Finally, not withstanding any of the above, the Picture solution isn't a
complete solution for me. As I mentioned, I need other controls like a
combo box which don't have Picture properties.

I have to believe there is someway to get the "native" controls onto a
worksheet.

Any other suggestions?

Thanks,

josh

 

[NickHK]

You are free to use any* ActiveX control that is installed on the user's
system and if you have an Installer routine, you can add new controls to
system. Obviously this becomes more complex than just sending a WB, but
installation should only be a one-time thing.

* Actually, there are some limitations in which Active controls you can use;
the control must expose the certain interfaces:
http://support.microsoft.com/kb/168392
Whilst is quite old, I believe it still applies to Excel.

NickHK

 

[Guest]

Hi Josh,

I ever have a tought like yours...
but it will be more simple in coding if you use a userform that embeded with
controls (OCX) on it.

But then I use VB6 form to make it realize... finnaly I get my XPvisual in
the Forms controls... with comctl32 V.5 in my environment of XP SP2.
then I used *.exe.manifest file to make it XP style.

Try that way... that I;ve tried but more complicated code in it.

 

[Josh Sale]

Thanks Nick. This sounds like music to my ears! A few follow-up questions:

- So would I create (say) such a command button using CreateObject?
GetObject? OLEObjects.Add?

- Do you know the name (file or class) of the "common" controls used by
WinXP? By Vista?

- The KB article you pointed me to indicates that these ActiveX controls
must support the IDataObject interface. Do you happen to know if WinXP's
"common" controls support this interface?

- Once I create one of these ActiveX controls, can I position it on the
worksheet the way I do my current controls (e.g., by setting its Top, Left,
Width and Height properties)? Will the Top and Left properties take values
from the same coordinate system as I currently use for the existing
controls?

- Once I create one of these ActiveX controls, will I be able to field
events as I currently do (e.g., Sub object_name_MouseDown(...))?

I'm sure I can figure out the answers to some of these questions once I get
started, but if you have any pointers I'd really appreciate it.

Thanks,

josh

 

[Josh Sale]

Hi Halim,

While my application has a number of VBA forms, there is also some
functionality which is most productively implemented with controls directly
on the worksheet.

So if you're suggesting that I move my controls from the worksheet to a
form, then I'm afraid that's not going to work for me. If I'm
misunderstanding your suggestion, can you please try again?

Thanks.

josh

 

[NickHK]

Basically, you need to:
- Search the web for suitable control(s), or write one yourself in a
suitable language (VB6 is relatively easy, if you have/can get it).
- Install on your machine, normally by registering the .ocx file
- On the Controls Toolbox in Excel, click the "More Controls" icon at the
bottom/right.
- Use as you would any other control, assuming it exposes the correct
interface. Whether it supports those events will depend on the writer of
that control.

You would of course have to ensure your users had said control installed on
their systems also. If you can distribute the control will depend on its
licensing terms.

As for the Win XP/Vista controls, I would doubt they are distributable and
installing on other systems would probably not be a good idea anyway.

NickHK

 

[Josh Sale]

Hi Nick,

Thanks for your continuing help. A couple of things:

- I don't think adding the ActiveX control to the Controls Toolbox does
anything for me. I don't put Excel into design mode and manually add these
controls. Instead all of these controls are programmatically added by my
add-in. This is why I was asking about what mechanism to use.

- All of my users are using WinXP so I wouldn't have to redistribute the ocx
that contains WinXP's common controls. What my users say is that they don't
want controls that work better or worse or look better or worse than the
"native" WinXP controls (command buttons, combo boxes, etc). This is why
I've been a little fixated on just using the WinXP controls.

Again I appreciate your help on this!

josh

 

[Josh Sale]

Nick,

I followed your suggestion and searched around a found an ActiveX control
that looks pretty promising. Its name is myCommand Button. I downloaded a
trial version and tweaked my code to create a myCommandButton instead of my
old Forms.CommandButton and other than dealing with some properties, of the
old command button that aren't present in the new command button, which my
code set, it just kinda, sorta works.

So this just might work out OK.

Thanks for the suggestions!

josh

 

[NickHK]

- You add them the same as any other ActiveX control.

- Can't say what your users consider better or worse.

NickHK

 

[Guest]

Hi Josh,
did you solve your problems with myCommandButton?
I've tried several Active X controls but they seldom work within Excel,
they are usually aimed for VB or other programming languages.
I tried myCommandbutton too but went "scared" by the consequence of the
message:
"You are about to initialize an Active X control that may not be safe......."
I trusted the control itself, but to get rid of the message you have to set
the
entire Office environment to accept UFI controls (Unsafe for Initialization).
I understand it as such that then is your system wide open to any ActiveX
control
that may sneak into your system!
It's a pity they don't do anything to the forms controls in Excel. I cannot
understand why anybody want an application to look like something from
Windows 3.1.
Nothing else does nowadays!
I've solved it partly by using a transparent button and a button sized
underlying image
with fading colours but they require a lot of work and are difficult to
maintain.

Best regards
Mats

 

[Josh Sale]

Hi Mats,

Its always kind of fun when an old dormant thread wakes up.

Obviously I understand and agree with your frustrations. I did some work
exploring all of this around the time of the original posting and felt like
I could make things work but then got dragged away to other assignments and
haven't gotten back to this. As I recall, I felt pretty good about
MyCommandButton (http://www.namtuk.com/mycommandbutton.aspx). The biggest
problem I thought I was going to have was changing my code to:

- instantiate the MyCommandButtons instead of the traditional MS command
buttons (no big deal), and

- avoid setting and testing various public properties (e.g.,
TakeFocusOnClick) which are present in the MS command buttons but absent in
the MyCommandButtons.

It all seemed pretty doable.

The other approach that has since surfaced, but which I haven't had time to
explore much, is to add .Net command buttons (maybe that's not exactly the
right term) to the worksheet using VB.Net and VSTO. One obvious implication
of this approach is that it doesn't support older versions of Excel, but we
may be getting to the point where we only support Excel 2003 and later.

If you make progress on this, please update the thread.

Good luck!

josh