# Off Topic > The Water Cooler >  >  Microsoft Excel OLEObjects Rant

## Mordred

Hi all,

    I have mentioned my problems in the past about using Sheet side ActiveX controls in that they are not stable and often change (either font size or object size) when different screens are used for viewing.  They are also unstable if you change their visibility properties.  I have started using shapes to rectify many of my problems because I can create my own check boxes, option buttons, and command buttons.  They are at least stable to the point that once you set them they will not change, no matter the resolution or whether or not they are going from visible to not visible.  I only wish I could create my own list boxes and combo boxes using shapes, that way I know things will be stable.

In saying all of that, how come Microsoft doesn't fix these types of problems?  Surely they must know that there are ActiveX stability issues, don't they?  Mayhap I should send them an email and let them know but I have no idea where to send the email.  At any rate, I think I am going to try and make a list box with shapes.  I have no idea how this can be done or if it can at all but I have to figure something out.  

To keep the spirit of my rant going, I have also used UserForms but have the same issues and they can also leave a residue (I don't know what else to call it) when you move them around.  I don't like userforms, at least not the Excel ones.

----------


## snb

> I have also used UserForms but have the same issues and they can also leave for residue (I don't know what else to call it) when you move them around



Why would you move around Userforms ????

----------


## Mordred

Well, it's not that I would move them around but I've made one where the user has moved it and complained about it.  My response to him was to not move it but he still does move it.  I also like the design control I have when using shapes, which you don't get from UserForms.

----------


## snb

```
Please Login or Register  to view this content.
```

----------


## Mordred

That is interesting snb.  I like how the userform moves back to center position.

----------


## snb

Me too.
In the attachment perhaps a more robust (you know what I mean  :Wink:  ?) alternative.

----------


## Mordred

I have to ask because, well, I just don't know (yet): How did you set the sheet to show and use only the range A1:E7?  I also like the form as it doesn't move at all.  There are still issues with the button object's font changing size, which will still detour me from using them.  Yesterday I started designing a "list box" using shapes.  It's coming along not too bad and most importantly, it doesn't go all wonky when the screen resolution changes.

----------


## snb

> I have to ask because, well, I just don't know (yet): How did you set the sheet to show and use only the range A1:E7? I also like the form as it doesn't move at all. There are still issues with the button object's font changing size, which will still detour me from using them. Yesterday I started designing a "list box" using shapes. It's coming along not too bad and most importantly, it doesn't go all wonky when the screen resolution changes.



1- hide the column/rows:
    rows(8).resize(rows.count-7).hidden=true
    columns(7).reszie(,columns.count-6).hidden =true

2. Issues with the object's font change  :Confused:  :Confused: . I never had those. What font do you use? I hope you realise that a missing font on a sytem will be replaced by another. So use the most common fonts and no exotic ones.

3. I never have anything to complain about userforms. I always keep them as small as possible (no scrolling). I never us more than 1 userform in an application. My impression is: the lesser VBA knowledge, the more supposed 'bugs'/mistakes by MS.

4. I don't think it's a problem using the zoom-function dependent on screen resolution. 'Imitating' userformcontrols looks to me a ('beginner's'  :Wink: ?) mistake.

----------


## Mordred

I always use Arial which is supported by pretty well everything that is computer!  As for imitating user form controls being a beginner's mistake, I would argue that a beginner wouldn't use shapes to create a user form.

----------


## snb

Hi Mordred, don't feel .....; see the smilies.
I'm intrigued by your question because I can't reproduce any of the problems you describe. Can you please post a workbook that contains a 'problematic' userform ?

----------


## Mordred

Hey its all good snb, I was just sayin!  :Smilie:   As for uploading a workbook, I could but this is not an issue that you'll come across unless you are going to increase the size of the viewing monitor and its resolution.  If you can do that, create a user form with a couple of buttons or any other object that can be changed with a click.  I have seen other posts in the past saying the same thing (but I cannot find them at the moment).  I think it is a small quirk that Microsoft hasn't dealt with yet.  

In saying all this, I can code (and have already) ActiveX objects to reload with the original properties after a click or a screen change but it is a pain in the backside to have to control them like that.  Also, and this is a personal preference of course, I like the look of shapes as a use form as well as the freedom of design that accompanies using shapes.  I also like the fact that you can do anything you want to shapes (almost).

----------


## snb

I tried to, varying from 800*600 to 1280*1024., but I don't see what you mean.
Can you please post some screendumps to illustrate your point ?

----------


## Colin Legg

I'll give an example of a well known ActiveX listbox bug. This is just an illustration.

Create a new workbook.
On worksheet sheet1:Add =ROW() into cells A1:A10.Add an ActiveX listbox and set the ListFillRange to Sheet1!A1:A10Ensure that you are no longer in design mode.
Change the formula in A1 to ="a" and observe that the listbox updates accordingly with no problem.Now change the zoom on Sheet1 to 75%.Change the formula in A1 to ="b" and observe that the listbox shrinks in size.

There are quite a few bugs such as this associated with worksheet-embedded ActiveX controls. My advice is to avoid using ActiveX controls on worksheets whenever possible: make use of controls on the ribbon/commandbars/menus. If a sheet really must contain a control, then use a Forms control (even these have some bugs, but they are less common than the ActiveX ones).

----------


## snb

@Colin

I thought we were discussing userforms... ? :Wink: 

I did the 'well-known problem'. No problem at all in Excel 2000.

----------


## Colin Legg

@snb
I saw a thread where Mordred was having issues with listbox text resizing. The listboxes were embedded on a worksheet.  :Wink: 

The shrinking listbox problem (different problem to the text one) definitely exists.... maybe you're not doing something 'right'.  :Smilie:  See if the attachment 'works' for you. Btw - this is just a demonstration, I am well aware of how to fix it.  :Wink:

----------


## romperstomper

@Mordred:
1. As Colin said, use Forms controls rather than ActiveX.
2. If you don't turn screenupdating off when loading the form, the user shouldn't see artefacts when they move it.  :Smilie:

----------


## snb

@Colin

Thank you for adding the file.
I opened it, I changed A1 into 'a'. No discernable problem (see the attachments). What am I doing 'wrong' ?? :Wink: 

PS. In this thread the argument elaborates on this statement of Mordred:




> I have also used UserForms but have the same issues and they can also leave a residue (I don't know what else to call it) when you move them around.

----------


## Colin Legg

@snb
No idea what you're doing 'wrong'. If you did it 'right' you get the attached effect.  :Smilie: 


There are plenty of bugs hanging around in userforms too, nothing to do with the developer's level (the level only allows for workarounds, not fixes - even if they can be worked around they are, nevertheless, bugs).

----------


## snb

@Colin

I retried to reproduce those 'phenomena', I wasn't able to do so. My system must have a built-in bugs-predator.
But thanks for pointing this out, because it warns me for the systems I deliver my solutions to.

----------


## Colin Legg

@snb




> I retried to reproduce those 'phenomena', I wasn't able to do so. My system must have a built-in bugs-predator.



lol... maybe you need to 'upgrade' to a newer version!  :Wink: 



@RomperStomper
If you have the time, please would you confirm if you can replicate the bug in versions pre XL 2003?

----------


## romperstomper

Only time I can do that is at home, where there are a few other calls on my time as you know, but I think I have a machine with 2000 and XP on it. Will test if I can.  :Smilie:

----------


## snb

@Colin

Maybe I can help you, providing my FM20.DLL so you can 'downdate' to a more robust version.

----------


## Mordred

Hey guys, sorry for leading this topic astray!  My problem mainly consists of problems with ActiveX sheet controls and not particularily user forms.  I mentioned user forms and the fact that I don't want to use them because they don't look as nice as a form that can be created with shapes.

@RS, I have use form controls but I (guess I'm fussy) don't really like the appearance of them.
@snb, can you come to Winnipeg and set up my computers so that it is like yours  :Wink: ?  I can't pay for your flight but I can buy you beer!   :Wink: 
@colin, thanks for stepping up and providing examples, you have proven that I am not going mad!  Or am I?

----------

