Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4669
  • Last Modified:

Excel VBA Active X Controls Resize

I typically try to use form controls when ever I can, but due to a few constraints with how the data needs to be structured, I'm forced into using an ActiveX combobox to leverage the 'Linked Cell' property to export the actual value instead of the number of the choice selected.

There are a lot of times when I've used ActiveX controls where they sometimes re-size (larger or smaller) once they've been clicked.  I've written code in the past to automatically re-size the object and font, but was wondering if anyone has experienced the same issue and if there's a quick fix or property change that will prevent this.

Any help is appreciated!

Thanks
0
Golfer219
Asked:
Golfer219
  • 13
  • 5
  • 2
1 Solution
 
dlmilleCommented:
Click Properties on the active-x control, then Set Integral Height to False and see if that doesn't improve your resizing problem...

Dave
0
 
dlmilleCommented:
This particular fix works for me at least for the listbox control...

Dave
0
 
Golfer219Author Commented:
Thanks Dave - appreciate the fast response - unfortunately, ComboBoxes don't offer Integral Height - but that is good to know for the future when using other controls.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
zorvek (Kevin Jones)ConsultantCommented:
This is a common issue with ActiveX controls and there is no workaround other than forcing them back to their desired settings as you are doing now.

Also note that using them can corrupt your workbook at random times. They were never designed to be used on a worksheet and yet Microsoft continues to make them available. Go figure.

Kevin
0
 
Golfer219Author Commented:
Very good to know - thanks Kevin - how bad is the corruption?  While working, I typically version workbooks 5-7 times a day for this very reason - is this a very common thing you see?
0
 
zorvek (Kevin Jones)ConsultantCommented:
The corruption is often bad. And it happens often enough that I never use ActiveX controls. I use forms before I use ActiveX controls. It just isn't worth the risk.

Kevin
0
 
dlmilleCommented:
Can you send an example without confidential data that I can play with a bit?

Dave
0
 
Golfer219Author Commented:
Hi Dave - I'll work on trying to get an example - it usually happens most often when you have multiple workbooks open and you trigger an event that drives code (i.e. clicking a command button will re-size everything, making a selection in a combobox re-sizes it).

Thanks!
0
 
dlmilleCommented:
Yea - I know what you mean, buttons, combobox, etc.  I just wanted to know which you are working with and a small example, then I could do research like I did on the ListBox when I had problems there and integralHeight works like a charm.

Perhaps there are other "charm"ing solutions :)

Dave
0
 
dlmilleCommented:
Here's a potential solution (not free) for a new combobox control which has as one of its features the capability to move/size with cells or note - so resizing can be managed.

In case you're interested: http://www.business-spreadsheets.com/solutions.asp?prod=82

here's another (not sure if its the same or not):  http://www.ozgrid.com/Services/excel-combobox-forms.htm

If I knew more what controls you were concerned about, I could focus my effort there.  I might be able to provide code to preserve a control's settings but not for all controls.

Buttons have an option to move or not move/size with cells - not sure you're having problems with buttons...

Dave
0
 
dlmilleCommented:
PS - those two links above are to the same wizard, lol

Dave
0
 
dlmilleCommented:
Try this - you and I both could have bought the suggested tool, but then we'd really have nothing to share for it and the knowledgebase (after all, this is about exchange of ideas/info/knowledge :), and for me, it was extremely satisfying to develop, though I'm holding my breath it helps you out with your problem.  Unfortunately, right now I don't have any examples of this 'problem' to test it on (other than one hefty app with a bunch of listboxes), though I tried to manually create 'problems' to see this fix it.

My testing - I changed IntegralHeight back to TRUE.  After moving back and forth between the sheets and doing operations, the listboxes went "quirky".  Then used this app on a couple of the sheets, and after re-activating a sheet that had fouled up, it reset back to its original settings!  Depending on your use, the event to use to restore settings might be as simple as user hitting a button (very visible), sheet selection (still visible, but nothing to do except get out and back to the sheet), or calculate.  Will need to think on what's most unobtrusive (as opposed to resource intensive, though with a handful of controls, it should not be a "big deal"...

The attached is an app I just developed, based on this question, (the app is initiated by the user via Developer's ribbon) that scans the active worksheet and stores "key" settings for all Active-X controls currently on the worksheet.  The settings are stored in hidden defined names, so they can be retrieved to "refresh" the control settings anytime the user activates that particular sheet (or, with minor modification, when the user runs the macro to restore the settings to their previously-saved state).

Settings that all the objects have in common (but not limited to) include:  "Height;Left;Locked;Placement;Top;Width"

Instructions for using the app and porting to your workbook are contained in the attached workbook.  In the attached workbook are examples of the commonly-used Active-X controls (the 11 that are readily pick-able on the from the insert->Active-X controls on the Developer's ribbon).  You can store their settings, then make control changes, move the controls, stretch them, etc. and if you move off and back on the sheet, you'll see that they are restored to their previously saved state.
Sample Worksheet to Demonstrate / Verify correctnessIt is my intent that when Excel "quirks" out and messes with these shapes, this app can restore to the "proper" state without the user even noticing (or, if the workbook_sheetactivate event is not used, then when the user prompts via the refreshControlsOnSheet() routine.)

I put this together as this is a recurring problem (I've had it, myself) and for personal development.  I learned alot, and got to use some techniques I've been developing on other utilities for this purpose (e.g., storing settings that Excel doesn't track for me, etc.) and - if it passes muster (crossing my fingers!) - then its a candidate for an article I'd like to write. So while you're sort of a test subject for the article/knowledgebase, the first priority is whether this will work for you or not!

Let me know how it works for you, and if you get your sample "quirky" spreadsheet going, please post for successive testing, as well.

Enjoy!

Dave
Maintaining-Control-of-ActiveX-R.xlsm
0
 
dlmilleCommented:
>>I've written code in the past to automatically re-size the object and font, but was wondering if anyone has experienced the same issue and if there's a quick fix or property change that will prevent this.

I see this in your original question, so perhaps my last submittal was not useful?  It might be worth checking out...

Let me know,

Dave
0
 
Golfer219Author Commented:
Hi Dave,

I think your code would work great - unfortunately I don't have the level of VB knowledge & experience to understand and implement it.  I am going to accept this as the solution though as I think it's the best option, especially for those who are advanced VB coders.

Thanks everyone!
0
 
dlmilleCommented:
Thanks.  I'm working an article on it (though I have a list I've been procrastinating) and will try to document such that a wider audience will be able to leverage...

Dave
0
 
Golfer219Author Commented:
I'd love to read it after you're done.  Thanks for all your help!
0
 
dlmilleCommented:
I reflected back on our chat and noticed I didn't give much direction in how to USE what I posted....

Note the sheet codepage:  ThisWorkbook.  Just copy that code you your ThisWorkbook codepage.

Finally, copy the module 1 module into your workbook.

You can then run the macro setControlsOnSheet.

I believe that's it.  Unless you add new controls (the rerun the above), anytime you select a sheet in the workbook, it will ensure their settings are reset to the past "set".

Note, when you make a change you want to keep - just run setControlsOnSheet again.

Let me know if this works as a decent explanation, pre-article :)

For the article, I'd put all this under an event class module, which should even be easier to implement, but that's later.

Dave
0
 
dlmilleCommented:
And the code will work for any number of sheets in the workbook where you implement it.

I'm going to get on that article, thanks to your concensual support!

:)

Dave
0
 
dlmilleCommented:
@Golfer - My article just got published.  If you find it helpful, please vote "YES"!!!

http:/A_5315.html

Dave
0
 
dlmilleCommented:
PS - At the end of the article, I provide an add-in to help make implementation easy, as well.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 13
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now