VBA controls are ...uncontrollable

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.

Thanks!
ohmetwAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
ohmetw,

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"
and
"Preventing a User from Moving, Resizing, or Deleting a Control on a Worksheet"
And
"Fitting a Control into a Cell"
... in this link:
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarexcel9/html/xlactivexl.asp

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

JeffCoachman
0
 
MalicUKCommented:
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.

Cheers,
MalicUK.
0
 
Patrick MatthewsCommented:
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
suited?
0
 
RyanProject Engineer, ElectricalCommented:
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?
0
 
ohmetwAuthor Commented:
nope, that does not fix the problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.