spinbutton.enabled = false not greying out

[RB Smissaert]

Using Excel 2003.
Have a Userform with many controls.
When doing enabled = false for all commandbuttons and spinbuttons, the
command buttons get greyed out, but the spinbuttons are not.
The code is like this:

Sub DisableAllControls()

Dim ctl As MSForms.Control

For Each ctl In MainForm.Controls
If TypeOf ctl Is MSForms.CommandButton Or _
TypeOf ctl Is MSForms.SpinButton Then
ctl.Enabled = False
End If
Next

End Sub

But even when I just set one individual spinner the result is the same.

They are actually disabled, but they just keep showing as normal. I have
tried to correct this with DoEvents and Userform.Repaint and using the Sleep
API function, but to no avail.
I presume I am getting to the limits of what a userform can hold and it is
starting to cause problems. On the other hand the whole file, a xla file is
not that big, only 1.65 Mb.
Would there be any solution for this other than doing Visible = False?
Thanks for any advice.


RBS

 

[Vasant Nanavati]

Hi RB:

Will this work?:

ctl.ForeColor = vbButtonShadow
ctl.Enabled = False

Kludgy, but I can'tr think of anything else.

Regards,

Vasant.

 

[RB Smissaert]

Vasant,

Thanks.
Tried it, but it didn't work either.
Looks I might be really stuck here.
I could construct a spinner from commandbuttons, but it doesn't seem worth
the trouble.

RBS

 

[Dave Peterson]

First, your code worked ok for me in xl2002.

While in design mode, can you change the .enabled property to false--then load
the form to see what it looks like?

(and a silly guess: Are you positive that it's a spinner and not just a
compressed scrollbar?)

 

[RB Smissaert]

It is a spinner and it works fine when I make a simple form with one
spinner.
It worked fine in 2002 even with the big form.

I can make it look disabled with:
ctl.ForeColor = -2147483631
But strangely when I combine this with
ctl.Enabled = False
it will look enabled again. It doesn't matter whether I do the color first
or the Enabled = False first.

Think I will stick with ctl.ForeColor = -2147483631 for now.


RBS