Solved

Excel VBA Active X Controls Resize

Posted on 2011-03-17
20
4,171 Views
Last Modified: 2012-05-11
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
Comment
Question by:Golfer219
  • 13
  • 5
  • 2
20 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 35162762
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35162765
This particular fix works for me at least for the listbox control...

Dave
0
 

Author Comment

by:Golfer219
ID: 35162878
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35162892
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
 

Author Comment

by:Golfer219
ID: 35162903
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35162908
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35166575
Can you send an example without confidential data that I can play with a bit?

Dave
0
 

Author Comment

by:Golfer219
ID: 35170163
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35172139
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35176149
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Expert Comment

by:dlmille
ID: 35176153
PS - those two links above are to the same wizard, lol

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 35177284
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35285475
>>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
 

Author Closing Comment

by:Golfer219
ID: 35479958
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35479975
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
 

Author Comment

by:Golfer219
ID: 35479994
I'd love to read it after you're done.  Thanks for all your help!
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35480610
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35480622
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35703392
@Golfer - My article just got published.  If you find it helpful, please vote "YES"!!!

http:/A_5315.html

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35703394
PS - At the end of the article, I provide an add-in to help make implementation easy, as well.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now