VBA controls are ...uncontrollable

Posted on 2007-07-30
Last Modified: 2013-12-26
I'm having issues with controls on an excel worksheet.  There are too many to shift to a form at this point but I am having issues when I use a projector...has something to do with the difference in resolution between my screen and the overhead projector screen res.  My either grow incrementally or shrink incrementally whenever a user clicks on them.  I have been able to control their size using conditional statements in VBA, stating if the control is not the proper width, then adjust the width and height to the correct value.
but even though I fix that, the fontsize is still incorrect.  how can I control this?  I tried

Shapes("shp1").texteffect.fontsize = 10 but that would not work.

so now I was thinking, maybe if a control could not be clicked on, it would not have these problems.  is there a way to control that?  like a control_Mouse_Down event?  ...something to release the focus before it happens?  any ideas?
I have a similar question out there, but this question has more detail and is meant to get to the bottom of my issues.  I know using a form is better, but I can't make the transition to that just yet...and I still need to utilize other features on the worksheet.

Question by:ohmetw
    LVL 23

    Expert Comment

    Hi ohmetw,

    You can turn unenable a control, which would stop the users from being able to click them.

    Just turn "Enabled" to False in properties.

    LVL 92

    Expert Comment

    by:Patrick Matthews
    ohmetw said:
    >>I know using a form is better, but I can't make the transition to that just yet...and I still
    >>need to utilize other features on the worksheet.

    It doesn't have to be an either/or proposition.  Why not leave on the worksheet that which
    is suited to the worksheet UI, and create UserForm(s) for that which the worksheet is not
    LVL 13

    Expert Comment

    Can't you set the size property to be fixed to the cells around it? so they'd only grow/shrink with the resizing of columns/rows?

    Author Comment

    nope, that does not fix the problem
    LVL 74

    Accepted Solution


    I had this same issue as well, with the projector and on the computer display.
    It usually happend with VBA controls that were sized very small.
    It also has to do with the resolution you used while creating the controls and the resolution you might be currently using to display them.

    The problem here is that the VBA controls (The ones in the Control Toolbox) are simply not designed to work in Worksheets, they are designed to work on VBA forms.

    While you can place them on Worksheets and they will "Function" properly, there is no assurance that they will "display" properly.

    Try using the Controls from the "Forms" toolbar, they cannot use code but in most cases they can be made to funtion like VBA controls.

    I realy don't think there is a Fix per se for this.
    But you can read the sections on:
    "Making a Control That Moves and Sizes Along with the Cell Under It"
    "Preventing a User from Moving, Resizing, or Deleting a Control on a Worksheet"
    "Fitting a Control into a Cell"
    ... in this link:

    Bottom line:
    If you are ready to use VBA code, you should be ready to use VBA Forms!


    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

    Suggested Solutions

    Title # Comments Views Activity
    How to debug this code 7 38
    Copy Value of cell in formula 1 27
    Using Pop Up Calculator in Excel 4 30
    Excel If tests 10 41
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    745 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

    13 Experts available now in Live!

    Get 1:1 Help Now