CODEWITHSUNDEEP
excelvba
Eka Oktavianus
Eka Oktavianus

Reputation: 21

Excel VBA, How to disable a commandbutton if one or more textbox is empty

I made a UserForm with 1 CommandButton and 4 TextBox, what I'm trying to do is: I want to disable the CommanButton if all 4 TextBoxs are still empty.

I already found code that works for only 1 text box:

Private Sub TextBox1_Change()
    If TextBox1.Value = "" Then
        CommandButton1.Enabled = False
    Else
        CommandButton1.Enabled = True
    End If
End Sub

When I use the same code in TextBox2, I am stuck, the CommandButton is enabled. When I use code (if TextBox1.value & TextBox2.value & TextBox3.value & TextBox4.value = "" then commandbutton1.enabled = false), the CommandButton is still enabled after filling TextBox1.

Upvotes: 1

Views: 6967

Answers (3)

Deb
Deb

Reputation: 131

Yes it's good but if you have a lot of text boxes I think this will be the right practice. As there is only 4 text boxes and assume their names are textbox1, textbox2, so on. But if the number of text boxes are a lot and they don't have identical names, it will help. Not only it checks for empty text boxes, it can check for any controls and their properties, like which radio button is checked or enable/disable any control. You need to make some small changes like replace msforms.textbox with your type of control and inside the if condition you can add your conditions.

Private Sub UserForm_Initialize()
Dim ctrl As Control
Dim x As Variant
Dim z As msforms.TextBox
x = 0
    For Each ctrl In frmtest.Controls
        If TypeOf ctrl Is msforms.TextBox Then
            Set z = ctrl
            x = x + CBool(Len(z.Text))
        End If
     Next
     CommandButton1.Enabled = x

End Sub

Upvotes: 0

Variatus
Variatus

Reputation: 14383

You don't need any IF condition.

CommandButton1.Enabled = cbool(Len(Textbox1.Text) + Len(Textbox2.Text) _
                          + Len(Textbox3.Text) + Len(Textbox4.Text))

CBool(x) = True while x is any number other than 0 in which case the result is False. If any of the text boxes has any content their combined length will be greater than 0 and the result of the CBool, therefore, True.

Upvotes: 5

Gary's Student
Gary's Student

Reputation: 96781

Use:

If TextBox1.Value & TextBox2.Value & TextBox3.Value & TextBox4.Value = "" Then

If the concatenation of the values is empty, then all the values must be empty.

Upvotes: 0

Related Questions