Disable Userform button

[Guest]

From another thread I worked how to disable and "grey out" a "commandbutton",
but I am using a Userforms "Button" is it possible to grey out this type of
button?

 

[Bob Phillips]

Exactly the same

Me.CommandButton1.Enabled = False


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob,

Sorry what is "Me"?

And for "Button 30" is it CommandButton30? Can you use CommandButton(1)?
--
Trefor

Exactly the same

Me.CommandButton1.Enabled = False


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

The keyword 'Me' refers to the object that contains it. So for a
class module, it refers to that instance of the class, for a
userform, it refers to the form, etc.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Bob,

Sorry what is "Me"?

And for "Button 30" is it CommandButton30? Can you use
CommandButton(1)?

 

[Guest]

Chip,

Sorry I am new to VBA and whatever you just said went way over the top of my
head!

As for Class modules, I have not got passed Modules yet. Can you help me out
this some code for a Module please?

 

[Dave Peterson]

Is that button on a userform?

If yes, is the code to disable the button behind that userform?

If yes, then Me refers to that userform.

If the button is on a worksheet and is a commandbutton from the control toolbox
toolbar and the code is behind that worksheet, then Me refers to that worksheet.

 

[Bob Phillips]

Trefor,

You may not know it, but userform code modules are a specific type of class
module. If you want to disable a button on a userform, there must be
something, some situation, that will determine when that button is to be
disabled. What is that?

Also, as you call it Button 30, it makes me think that you are referring to
worksheets not a userform at all. Can you clarify?



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peter T]

Trefor,

Following Bob's observation about your button named "Button 30", I guess it
is neither a userform button nor a Commanbutton but a button on a sheet
applied with the Forms toolbar. If so try this in a Normal module

Sub TestEnable()
EnableButton "Button 30", True

End Sub

Sub TestDisable()
EnableButton "Button 30", False

End Sub

Function EnableButton(sBtnName As String, bEnable As Boolean)
Dim btn As Button

On Error Resume Next
Set btn = ActiveSheet.Buttons(sBtnName)

If btn Is Nothing Then
MsgBox sBtnName & " does not exist on this sheet"
Else
btn.Enabled = bEnable

'simulate greyed out text if disabled
btn.Font.Color = IIf(bEnable, 0, RGB(150, 150, 150))
End If

End Function

Regards,
Peter T

 

[Guest]

Peter/Bob/Dave,

Thankyou all for your replies and yes my "new guy" terminology finally got
deciphered by Peter, yes indeed a “sheet with a button from the forms
toolbarâ€.

Dave, Currently I set a variable to “disabled†on a certain event, each
macro that runs from the button checks this variable and if set to “disabledâ€
simply does an Exit Sub and the macro obviously does not run.

Peter your code does indeed disable the button and forces the colour of the
text to grey and then back to black afterwards, thankyou for this.
Unfortunately the button colour is not black (its blue) and in some cases has
two different colours (Red and Blue). The disable on the CommandButton on a
sheet from the Control Toolbox seemed to be neat and easy, but clearly not so
neat on a button from the forms toolbar? I presume there is a way to check
the colour on the button, save it to a variable and return it when finished,
but I presume this would be all too hard with multiple colours?

 

[Dave Peterson]

worksheets("sheet1").buttons("button 1").enabled = false

For the easy response.

 

[Guest]

Dave,

Indeed a simple one line version of Peter's code, but this does not "grey
out" the button. To me this leave the user wondering why the button is not
working, "grey'd out" makes it clear that it has been disabled for some
reason.

Apart from the early Exit Sub, I have a Msgbox, stating that the button had
been disabled, I just thought there may be a more elegant way of doing this

 

[Tom Ogilvy]

Try to stay with the discussion Dave. <g> Disabling a forms button does not
change the appearance.

to the OP, You can certainly store the information on the color of the Font
of the button before you change it and then use that information later to
change it back. Where you should store it would depend on what your code
is doing and how long it will have to be stored. (will the workbook be
closed in the meantime for example).

Also, the information on Forms buttons was provided earlier in response to
your hide a button thread. If your going to start threads, please have the
courtesy to check for answers.

 

[Guest]

Many thanks all for your replies, it look like I may have to stay with what I
have.

Tom, I think its called Time Zone, your update to my other Q was after I
went to bed, 24hrs later I have signed on for an hour or so and have been
working through the list of replies to my five questions, I just happened to
get to this one first. Its now 00:55 for me and I am going to bed, my
appologies if I offended it was not my intention to do so. This is a great
forum and I have certainly been very impressed with the quality of the
responses I have been getting and hope to continue getting. BTW thankyou for
the reply to "Re: VBA code to set a reference", which I also did reply to and
am awaiting your reply!

--
Trefor

Try to stay with the discussion Dave. <g> Disabling a forms button does not
change the appearance.

to the OP, You can certainly store the information on the color of the Font
of the button before you change it and then use that information later to
change it back. Where you should store it would depend on what your code
is doing and how long it will have to be stored. (will the workbook be
closed in the meantime for example).

Also, the information on Forms buttons was provided earlier in response to
your hide a button thread. If your going to start threads, please have the
courtesy to check for answers.

 

[Dave Peterson]

That's why I took the easiest answer to just disable the button. (I'm sorely
confused following the various threads!)