Disable button on a userForm

Question

I'm trying to figure out how to disable a button within my userForm if a certain cell within my spreadsheet equals a certain number. I tried the code stated below, but it isn't working.

Private Sub UserForm_Initialize()
Label2 = Sheets("DATA").Range("AM2").Value
Label4 = Sheets("DATA").Range("AO2").Value
Label7 = Format(Sheets("DATA").Range("R8").Value, "Currency")

If Sheets("DATA").Range("AL10").Value = 10 Then
ActiveSheet.Shapes("CommandButton1").Select
UserFormact_Upgrade.CommandButton1.Enabled = False

Else


End If

End Sub

Tiago Cardoso · Accepted Answer · 2011-04-05 18:30:53Z

6

Your code should be working, as you're on the right path.

To test it, simply create a new form and add this code, you'll see it should work. Maybe you're having problems within the IF clause?

Besides, you don't need to select the shape prior to disabling it; just disable it right away.

Private Sub UserForm_Initialize()

    CommandButton1.Enabled = False

End Sub

answered Apr 5, 2011 at 18:30

Tiago Cardoso

2,1061 gold badge16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary Nolan · Accepted Answer · 2016-09-23 20:21:05Z

I know this is old, but got to this thread trying to solve my problem, and found a solution that wasn't mentioned here. So in case someone gets here like I did, and this didn't quite get them where they needed to go, I thought this might help.

I had a userform with a drop down box called cmdADAMFields, and I didn't want my submit button called FieldsSubmitButton to be enabled until I selected something from the dropdown box.

I had to break up my argument into two different private subs vs one larger If-Then-Else statement.

First, I put:

Private Sub UserForm_Activate()
If cmbADAMFields.ListIndex = -1 Then FieldsSubmitButton.Enabled = False
End Sub

Then when for my pulldown's private sub when it's value changed I wrote:

Private Sub cmbADAMFields_Change()
FieldsSubmitButton.Enabled = True
End Sub

Paulo Buchsbaum · Accepted Answer · 2012-11-15 18:04:46Z

The proper place for setting Enabled property is in Activate event (associated with Show method) and not Initialize event (associated with Load instruction). The below code disable the button CommandButton1 when AL10 cell >= 10.

        Private Sub UserForm_Activate()
           CommandButton1.Enabled = ( Sheets("DATA").Range("AL10") < 10 )
        End Sub

For buttons you can choose between normal buttons (property Enabled=False and property Visible=true), disabled buttons (property Enabled=False and property Visible=true) and invisible buttons (property Enabled=False and property Visible=False), that it is a cleaner interface, in most cases.

Concerning text boxes, besides normal, disabled and invisible status, there is a locked status, that is enabled and visible, but cannot be user edited. (property Locked = True)

A locked control only can be changed by VBA code. For instance, someone can includes date text boxes, that it's filled using a secondary popup date form with Calendar control.

Collectives™ on Stack Overflow

Disable button on a userForm

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related