Buttons shrinking and checkboxes swelling...

[Geoff]

WT heck...do I have some kind of disease? As I'm using a button, I am
watching it as it disappears away to nothing. In time, it will have collapsed
into a singularity and I fear for the future of the planet. Should I call
CERN?

And my check boxes are growing with every click. Is this just more evidence
for the accelerating expansion of space-time?

Seriously...wt heck. I've set them all to not move or size with cells
(though the cells are not moving and resizing anyway).

 

[Dave Peterson]

Lots of people have said that if they deleted the controls and re-added them,
the problem went away (at least temporarily!).

I find that the controls from the Forms toolbar are much better behaved. I bet
you're using controls from the Control Toolbox toolbar.

If you don't need all the features that the controls from Control toolbox
toolbar provide, you may want to replace them with the controls from the Forms
toolbar.

 

[Geoff]

Thanks Dave.

Since they are integral to code that I have, the ActiveX Controls are the
way to go or so I was told in a previous thread. I've tried using Form
controls and if I name them the same as the ActiveX controls, the code
doesn't recognize them. I'm sure there's a way to refer to them in VBA, but I
don't know how.

For example, I have a check box, cbPower that I refer to:

Latency = IIf(cbPower, Range("PourFactor"), 0)

If I change it to a Form control called cbPower, when the code runs it kind
of scratches its head and tells me there is no such object or something like
that.

So, what's going on her? Do I need to use controls that are not as suitable
for my needs because Microsoft software is kludgy? I mean come on, I paid
several hundred dollars for this. I should expect one of the main features to
work properly, right?

 

[Dave Peterson]

First, if you have controls (like commandbuttons from the control toolbox
toolbar) and if you replaced them with buttons from the Forms toolbar, code for
these would have to modified and moved to a general module. Then you'd assign
the macro (in that general module) to the button. (Rightclick on the button and
choose assign macro.)

But if you're only using controls like checkboxes for an indicator, you could
replace them with checkboxes from the Forms toolbar.

Your code would have to change to something like:

latency = iif(cbool(me.checkboxes("cbpower").value = xlon), _
me.range("Pourfactor").value, 0)

I don't like the iif statement. It turns out that it's slower than the a
"normal" if:

latency = 0
if me.checkboxes("cbpower").value = xlon then
latency = me.range("pourfactor").value
end if

Since I used the Me keyword, this code assumes that the code is still in the
worksheet module.

If I wanted to refer to the checkbox in a general module, I could use:

dim wks as worksheet
set wks = activesheet 'or worksheets("whatever")

if wks.checkboxes("cbpower").value = xlon then
...

==========
Just think of all the people who spend 1000's of dollars on new cars. And find
the little (and sometimes major) problems.

Every complex technology is going to have bugs--including excel.

 

[JLatham]

Geoff, I hope this helps rather than confuses, so if it confuses, just ignore.

Start by making a copy of all of the code associated with your current
ActiveX controls into something like a Notepad file. Add extra notes so you
know what code went with which control and for what purpose. This gives you
the code you'll need later on for the controls from the Forms toolbar (which
I typically prefer to use also).

Now, add a control from the Forms toolbar. One of the first things it's
going to ask you for is a macro to assign to it, and one really doesn't
exist, so choose NEW and what will happen is that it will place a code "stub"
like:

Sub Button1_Click()

End Sub
into a standard code module. You can either then copy the code within the
original control's Sub... End Sub statements into that stub, or defer that
operation until later. You can always get back to that code by
Right-clicking on the control and choose [Assign Macro] and then the [Edit]
option to take you right into the code or code stub for that control.

I do hope this helps.

 

[Geoff]

I don't like the iif statement. It turns out that it's slower than the a
"normal" if:

Interesting. I never woulda thunk it. I'll change them. I figured the IIF
would be faster since it's a single statement, but I prefer the normal IF
since it's a structure I'm familiar wit going back to my Fortran (with punch
cards no less) days.

Just think of all the people who spend 1000's of dollars on new cars. And find
the little (and sometimes major) problems.

Every complex technology is going to have bugs--including excel.

Oh, is my Excel under warranty? ;-)

I tell ya though, I do love you guys as a resource. I learn more every time
I come here. It sure beats the Help that comes with Office/Excel....another
gripe.

Cheers and thanks!

 

[Dave Peterson]

I didn't do the testing on the IIF stuff, but someone (maybe Charles Williams of
http://www.decisionmodels.com/calcsecretsh.htm fame????) did and posted in one
of the excel newsgroups.

Just as an aside, most(?) software companies do support their products longer
than lots of manufacturers--the service packs are usually free. And sometimes,
new versions are available at a cheaper price if you own an older version.

I don't think most auto dealers would give you much of a price break for being
an existing customer.

Yeah, I understand that there are lots of bugs in lots of programs. But I know
most of the problems that I've had with excel haven't been MS's fault. They've
been my problems.