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

Hi RB:

Will this work?:

ctl.ForeColor = vbButtonShadow
ctl.Enabled = False

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

Regards,

Vasant.



"RB Smissaert" wrote in message
...
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,

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


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Hi RB:

Will this work?:

ctl.ForeColor = vbButtonShadow
ctl.Enabled = False

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

Regards,

Vasant.



"RB Smissaert" wrote in message
...
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

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 wrote:

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

--

Dave Peterson

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


"Dave Peterson" wrote in message
...
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 wrote:

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

--

Dave Peterson