Learn how to a build a cloud-first strategyRegister Now


VBA controls are ...uncontrollable

Posted on 2007-07-30
Medium Priority
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

ID: 19593182
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 93

Expert Comment

by:Patrick Matthews
ID: 19593790
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

ID: 19593795
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

ID: 19594400
nope, that does not fix the problem
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 20303458

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

810 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