<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction")

Published on
65,677 Points
52,477 Views
17 Endorsements
Last Modified:
Awarded
Community Pick
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction")
David Miller (dlmille)

Intro

Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls readily available to the Excel user from the Developer Ribbon, or the Controls ToolBox).  It’s certainly understandable when users lose their cool when “quirky” things happen to their applications as a result of Active-X controls going awry.

It is my intent that when Excel "quirks" out and messes with these shapes, this utility can restore Excel to the "proper" state without the user even noticing (or, if the workbook_sheetactivate event is not used, when the user prompts via the refreshControlsOnSheet() routine.)

I’m a fan of using Active-X controls in my worksheets, in spite of some of their “quirks”.  One key quirk that appears to affect folks more often than not is the resizing or presumed “disappearance” of controls that can sometimes happen for “unexplainable” reasons.  

I’ve had controls “go weird” and get bigger/smaller and even disappear when I’ve connected via Remote Desktop into an Excel instance, or used an Excel app with lots of images and controls – and other times for no apparent reason (I wasn’t doing anything unusual and there were only a couple controls on a newly-built spreadsheet).
 
“Weird”, I would mutter as I restarted Excel or rebooted hoping it would all “go away”, which it generally did.  It wasn’t until I notice others having this problem that I took a greater interest in providing some relief (while not perfect, at least we have a way to move on and get work done without throwing out our design!)

While the ListBox has an IntegralHeight property whose side-effect of a FALSE setting will keep that control from going askew, and while command buttons have properties such as move/size with cells, etc., other controls are not as graceful.

In the following, note how each control has been shaped, presumably “nice and tight” as if part of an Excel Spreadsheet app:

Looks "good"!
At some point, you may have experienced controls “going awry” upon returning to a sheet, at some point in the future:
Not so good...
Wouldn’t it be great, if we could just ensure all controls on the sheet are set properly, as designed, anytime we come back to this sheet?  As part of an event, so it just takes care of itself?

I addressed a question recently http:/Q_26895496.html with a goal to dealing with the “quirk” and while this solution doesn’t get to the root cause, it does provide relief to the point that this particular “quirk” can virtually go unnoticed.

Every Active-X control has similar properties, and the ones we care about for this exercise are Top, Left, Height, Width, Placement and Locked.  Leveraging some techniques I’ve been developing, we can use the set of routines discussed in this article to “record” those settings and ensure those pesky controls STAY PUT the way we designed them to be (their size, location, etc.) on a “permanent” basis.

First, I'll explain how to use this utility, and then later, I’ll discuss the code and provide an attached spreadsheet for your use.

1. How to Implement

Using the VBA Project Explorer (ALT-F11), and the code (later in this article):
1.  Copy the module:  SustainControlSettingsFunctions to your workbook.  If you use the attachment, you can just drag this module from the example into your workbook.
2.  Copy the Workbook_SheetActivate() code to your workbook, and paste it into your ThisWorkbook code page.

That's it, from a coding standpoint!

Now, lets' get back to your spreadsheet and see what's next:
1.  Create whatever controls are going to be on the worksheet, and
2.  At any point, run the setControlsOnSheet() routine, to either initially store the settings for all controls, refresh those settings, or add new settings (as it does this for every control on the active sheet).  Note:  to keep things simple, let's just run the setControlsOnSheet() macro from the Tools->Macro (pre-Excel 2007) or Developer's Ribbon (Excel 2007+).  At this point, you could add a short-cut key to execute the setControlsOnSheet(), by selecting Options and identifying the control sequence you can press to execute the macro in future.

That's it!  You've now added a level of "security" to the controls you've developed on your important and impressive Excel application!

Care should be taken to ensure all settings "look right" (e.g., Excel has as yet to get "quirky", or the user has just adjusted one to many of his controls and is ready to "save" their settings.)  Otherwise, any improperly sized controls' settings would get stored or overwritten.


As an enhancement, this app could be embedded in a class module (see attachment II), or even better - as part of an add-in - thus keeping any related code out of the users "normal" programming environment.  The sheet activate event trapping would be captured in the class module, rather than the user having to add it to his/her ThisWorkbook module.

2. How it Works

Rather than make this routine process intensive (by putting event traps in every sheet created), the ThisWorkbook event for sheet activate will "reinitialize" all settings for all controls that exist on any sheet tab selected.  No action would be taken, if no control settings were stored on a given sheet.  As a result, the control settings on the sheet will be "restored" to their most recently saved settings, thus (hopefully) "forever" avoiding the Excel "quirky" resizing consequence.

Here's what you can paste in your ThisWorkbook codepage using the VBA Project Explorer:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.EnableEvents = False ' to avoid any code associated with a control which may fire as a result of changing settings
    
    Application.Run "RefreshControlsOnSheet", Sh
    
    Application.EnableEvents = True
End Sub

Open in new window



This is made possible by three simple routines, and a sheet change event (note, you could enhance this to refresh settings on demand, or other event that makes the most sense for your application & style):

1.  Public Sub setControlsOnSheet() 'the DRIVING routine that ensures all Active-X controls get their settings stored in a named range
2.  Private Sub storeControlSettings(sControl As String) 'Called by the setControlsOnSheet() routine and does the work of pulling current settings and storing them, for one control
3.  Private Sub refreshControlsOnSheet(sh As Object) 'Called by an event or action to retrieve and then refresh control settings to their previously saved state

The routine setControlsOnSheet():
1.  Obtains the 6 common control settings, for every OLEObject (Active-X) control on the active sheet, and
2.  Stores those settings into a string array, sControlSettings(), and
3.  Adds/updates a defined name (which is hidden) with those settings.

The routine refreshControlsOnSheet():
1.  Cycles through existing OLEObject control on the active sheet and
2.  Retrieves any previously stored settings into a string array, sControlSettings(), and
3.  Updates each respective control with those settings.

Storage and Retrieval of control settings is managed through defined names, which are not visible to the user.  The defined name for each control on a sheet is built up based on the active sheet name and the control name, creating a unique instance.

Here's the code you can insert into a new module in your workbook (or just drag and drop the SustainControlSettingsFunctions module from the attached demonstration file into your workbook's VBA Project Explorer area):

 
Option Explicit
Const CONTROL_OPTIONS = "Height;Left;Locked;Placement;Top;Width" 'some potentially useful settings to store and sustain
Private Sub refreshControlsOnSheet(Sh As Object)
'routine enumerates all objects on the worksheet (sh), determines which have stored settings, then refreshes those settings
'from storage (in the defined names arena)
Dim myControl As OLEObject
Dim sBuildControlName As String
Dim sControlSettings As Variant

        For Each myControl In ActiveSheet.OLEObjects
            sBuildControlName = "_" & myControl.Name & "_Range" 'builds a range name based on the control name
            'test for existance of previously-saved settings
            On Error Resume Next
            sControlSettings = Evaluate(sBuildControlName) 'ActiveWorkbook.Names(sBuildControlName).RefersTo 'load the array of settings
            If Err.Number = 0 Then ' the settings for this control are in storage, so refresh settings for the control
                myControl.Height = sControlSettings(1)
                myControl.Left = sControlSettings(2)
                myControl.Locked = sControlSettings(3)
                myControl.Placement = sControlSettings(4)
                myControl.Top = sControlSettings(5)
                myControl.Width = sControlSettings(6)
            End If
            Err.Clear
            On Error GoTo 0
        Next myControl
        
End Sub
Private Sub storeControlSettings(sControl As String)
Dim sBuildControlName As String
Dim sControlSettings(1 To 6) As Variant ' set to the number of control settings to be stored
Dim oControl As Variant

    Set oControl = ActiveSheet.OLEObjects(sControl)
    
    'store the settings to retain, so they can be reset on demand, thus avoiding Excel's resizing "problem"
    'create array of settings to be stored, with order dictated by CONTROL_OPTIONS for consistency/documentation
    
    sControlSettings(1) = oControl.Height
    sControlSettings(2) = oControl.Left
    sControlSettings(3) = oControl.Locked
    sControlSettings(4) = oControl.Placement
    sControlSettings(5) = oControl.Top
    sControlSettings(6) = oControl.Width
    
    
    sBuildControlName = "_" & sControl & "_Range" 'builds a range name based on the control name
    
    Application.Names.Add Name:="'" & ActiveSheet.Name & "'!" & sBuildControlName, RefersTo:=sControlSettings, Visible:=False 'Adds the control's settings to the defined names area and hides the range name
    
End Sub

Public Sub setControlsOnSheet()
Dim myControl As OLEObject
Dim xMsg As Integer

    xMsg = MsgBox("This app will store key settings for all controls on your active worksheet, as they CURRENTLY exist.  Are you sure you want to continue (will overwrite past settings)?", vbYesNo, "Hit Yes to save control settings...")
    If xMsg = vbYes Then
    
        For Each myControl In ActiveSheet.OLEObjects 'theoretically, one could manage settings for all controls of this type...
            storeControlSettings (myControl.Name)
        Next myControl
    
        MsgBox "Settings for current controls on sheet have been stored", vbOKOnly
    End If
    Application.EnableEvents = True 'to ensure we're set to "fire" on worksheet changes
End Sub

Open in new window


And here's the attached workbook, complete with code and a sample worksheet with a suite of Active-X controls setup for you to play with (try moving a control or resize it, then click to another tab and come back - as the settings are saved, they should revert to original condition):

Attachment I: Demonstration Sample

SustainControlOfActiveXResizing.xls  

Attachment II:  Demonstration Sample - code implemented as an add-in, to be less intrusive
 SustainControlOfActiveXResizing-.xla
 

Enjoy!

David Miller (dlmille)
17
Author:dlmille
  • 3
  • 3
  • 3
  • +7
17 Comments

Expert Comment

by:mpavey
Thanks for this!

Unfortunately I ended up disabling it, however. I discovered that it rendered the clipboard unusable. I would be in workbook A, select some cells, hit Copy, then switch to workbook B -- the one in which I implemented this code. I wanted to Paste, but the option was not available.

One other thing I found was that it was only by updating the Height property to be +1 and then back down to its correct value did the button regain its correct size.

Also, the documentation in the sample file says to run the wrong subroutine in order to create the stored correct values. (The text in this page is correct though.)
0
LVL 42

Author Comment

by:dlmille
You're welcome.

>>Unfortunately I ended up disabling it, however. I discovered that it rendered the clipboard unusable. I would be in workbook A, select some cells, hit Copy, then switch to workbook B -- the one in which I implemented this code. I wanted to Paste, but the option was not available.

Here's what you can do, to avoid having issues with the Clipboard or Undo stack: Change the approach by which you update your controls if there is a "mess up".  So, rather than resetting the controls on the Worksheet_Activate(), you can run ResetControlsOnSheet() by  1) adding a menu item to the add-in menu, 2) add a Command Button, or 3) Call the macro directly from the Developers menu (or Tools->Macros menu) and therefore the resetControlsOnSheet() routine would only run on your "demand".

So, comment out the code in the ThisWorkbook codepage, and add this to a public module, linking it to a menu item or button, etc.:

sub runResetControls()
    Application.EnableEvents = False ' to avoid any code associated with a control which may fire as a result of changing settings
    
    Application.Run "RefreshControlsOnSheet", ActiveSheet
    
    Application.EnableEvents = True

Open in new window


The same approach should be taken if you're using the add-in, just comment out the SheetActivate Event in the CExcelEvents class module, then call your refreshControlsOnSheet() routine on "demand".

>>One other thing I found was that it was only by updating the Height property to be +1 and then back down to its correct value did the button regain its correct size.

That may be in the instance you tested, but its not a guaranteed "fix" for the control.

>>Also, the documentation in the sample file says to run the wrong subroutine in order to create the stored correct values. (The text in this page is correct though.)

You'll have to clarify as I cannot find this omission.  About the only thing I pick up is to run the setControlsOnSheet() routine which would be correct.

Cheers,

Dave
0

Expert Comment

by:Rayne
Thank you Dave....
0
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Expert Comment

by:mm77089x
Thanks Dave...

I am trying to resolve an issue dealing with the quirkiness of activex controls in excel 2010.  My controls resize when I am using a projector (screen resolution changes).  I have tried your solution and workbook but they both are not working when testing using projector / different resolution.  

Any ideas?

Thanks,
Robert
My Website: Roth 401k
0

Expert Comment

by:Maliki Hassani
Dave, you are the man!!  Great save today!
0
LVL 42

Author Comment

by:dlmille
LANCE_S_P - thank you for your kind words.

mm77089x - you might read this thread: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27757257.html

And perhaps post there to ask Rayne if he did anything else to resolve the issue.

However, I would suggest plugging the computer into the projector and aligning resolutions, then pull up your Excel file and see if you have any resizing issues.  I have, as yet, to duplicate the problem that you and Rayne see, so hopefully as a result of that thread and/or my suggestion, you get the solution you need.

If not, I suggest posting a question to get other E-E folks involved.

Dave
0

Expert Comment

by:Rayne
Thank you Dave :)

mm77089x  - I guess you are in the same exact boat where I was in :)

Unfortunately, later I decided to remove the active X components as it was resizing gigantically for some strange reason.

Word of Advice: If you would be displaying your excel file to your important clients over a projector, I would suggest you should use non active X components.

Unfortunately, the programmers at Microsoft designed the active X controls to work on user forms only. They are not meant to be working on a worksheet.

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Windows/XP/Q_26921180.html
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26699908.html
0

Expert Comment

by:Rayne
mm77089x


My case: I had a lot of axtiveX controls, so I decided to put them in a form instead of the worksheet.
I have not used Dave's code yet. But I think it will resolve your issue.
0

Expert Comment

by:bushido121
This is great!  Thanks David.
0
LVL 56

Expert Comment

by:Martin Liss
Nice work.
0
LVL 56

Expert Comment

by:Martin Liss
I've run into a problem when using an ActiveX listbox on a sheet. In the attached workbook if you double-click on one of the listbox's entries you are sent to Sheet2 and clicking the command button on Sheet2 takes you back to Sheet1. I think I have everything from your article in place and I've run setControlsOnSheet, but while the listbox size doesn't change, the font appears to get smaller and smaller the more times you go back and forth. I say "appears" because investigating the properties of the listbox shows the font the same way it was originally which is Calibri, 16, bold. Can you explain why or suggest a what I can do to correct it.
Problem.xlsm
0

Expert Comment

by:Sofia Atknina
Hi, thank you for help! I hope report users will test the file and all will work well.
For now, I've got "Private Sub Workbook_Open() " which opens workbook at specified tab <First> on open regardless the tab report last closed.
After I inserted the 2 codes above this Sub doesn't work anymore. How can I fix this?

Private Sub Workbook_Open()
Application.GoTo Sheets("First").Range("A1"), True
End Sub
0

Expert Comment

by:Bjørn Austrud
Add this code to a .reg file. Double-click and and confirm. Restart Excel.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\options]
"LegacyAnchorResize"=dword:00000001

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Common\Draw]
"UpdateDeviceInfoForEmf"=dword:00000001
0

Expert Comment

by:Alfred Froembgen
Hi David. First, I used your code and all works perfect. Really nice solution, well done;). One question is remaining for me. In all-days use, I share this workbook and if shared, the code does not work. Any idea how I could solve this? I resize commanbuttons, tried locked with true and false, no error appears, just misses resizing action. Programmed and tested with O2007 under xp and w7. Also any other code I tried missed, when any resizing is wanted while workbook is shared. Well, I'm out of ideas now :( . Can you help?
0
LVL 56

Expert Comment

by:Martin Liss
Alfred, there's another approach you can take. In this article of mine I describe how to "float" and ActiveX control over any and all cells where you want it to appear. Using this approach I've never experienced, and nobody has reported, any of the ActiveX quirks.
1

Expert Comment

by:Alfred Froembgen
Hi Martin. Thx for your comment, I will soon have a closer look at it. Possibly a good idea for new projects. Changing my complete menu-system will me muuch work;). Another article speaks about fixing the issue with grouping the elements. I'll have a try with this and later on give feedback. Still also hoping David storming his brain....
0

Expert Comment

by:Alfred Froembgen
Here I'm back. Astonished. In my case a lot of commandButtons disbehaved. At least resizing is stopped, if these are grouped. Hope it also helps others. Greets, Alfred
0

Featured Post

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month