spinbutton enabled = false not changing colour

[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.
Would there be any solution for this other than doing Visible = False?
When do exactly the same on a little demo form all is fine.
Thanks for any advice.


RBS

 

[Harald Staff]

Hi RB

Can't reproduce the error here, your code works fine.
What is "many controls" ? If that is 200 some, then my experience is that
userforms may get unstable.

HTH. Best wishes Harald

 

[RB Smissaert]

It is over 200 controls.
Apart from this everything else works fine.
It isn't really a major problem as the spinners are disabled
and I can set the backcolor, but I would like to fix if possible.

RBS

 

[RB Smissaert]

Try again, but now when the spinbutton has a backcolor set before doing
Enable = False.
I picked this backcolor: 15001309, but may not matter.
So, this trouble is caused by the backcolor being set, not anything else.
Note that the commandbutton doesn't have this problem.

RBS

 

[Harald Staff]

Try again, but now when the spinbutton has a backcolor set before doing
Enable = False.
I picked this backcolor: 15001309, but may not matter.
So, this trouble is caused by the backcolor being set, not anything else.
Note that the commandbutton doesn't have this problem.

I see. That makes kinda sense.
But "don't" then <g>. Some things in life doesn't combine and we usually
have to live with that.
Thank you for the insight though. We live and we learn.

Best wishes Harald

 

[RB Smissaert]

I need the backcolor, but doing:
ctl.ForeColor = vbButtonShadow
simulates a disabled state and that is good enough in my case.
Still, always interested in a better solution.

RBS

 

[Harald Staff]

Ah! Clever workaround. Thanks.

Best wishes Harald

 

[RB Smissaert]

Strangely, if you do:
ctl.Enabled = False
ctl.ForeColor = vbButtonShadow

the ForeColor won't be vbButtonShadow.

Same if you do:
ctl.ForeColor = vbButtonShadow
ctl.Enabled = False

RBS