Enabling the Command Button when 4 Cells are not Empty

Question

I have 4 Cells (S11:T12) and a Command Button 1, what I want is, until all 4 cells are populated the command button should be disabled (Which I can do from the properties tab) and once all 4 cells are filled with number, the command button should be enabled and once the data from these cells are deleted, the command button should be disabled again.

Under which event should I write the code?

I tried this, but it does not work.

Private Sub Workbook_Open(Cancel As Boolean)

    If Sheets("WorkArea").Range("S11:T12") = "" Then
        Sheets("WorkArea").CommandButton1.Enabled = False
            Else
        Sheets("WorkArea").CommandButton1.Enabled = True
    End If
      
End Sub

Either validate the cell values one cell at a time or concatenate the all the cell values then do the validation. — EEM, Nov 14 '20 at 05:18
Suggest to use the `Worksheet_Change` event. It will be triggered for every change into the worksheet. — EEM, Nov 14 '20 at 05:37
I agree with @EEM use [Worksheet_Change](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) — Siddharth Rout, Nov 14 '20 at 06:19

score 2 · Accepted Answer · answered Nov 14 '20 at 07:06

Use the WorkSheet_Change event handler to handle the change in cells, and you can use the CountBlank worksheet function to determine if a range is empty.

Private Sub Worksheet_Change(ByVal Target As Range)
    If WorksheetFunction.CountBlank(Range("S11:T12")) = 4 Then
        Sheets("WorkArea").CommandButton1.Enabled = False
    Else
        Sheets("WorkArea").CommandButton1.Enabled = True
    End If
End Sub

According to your question however, you actually want:

Private Sub Worksheet_Change(ByVal Target As Range)
    If WorksheetFunction.CountBlank(Range("S11:T12")) = 0 Then
        Sheets("WorkArea").CommandButton1.Enabled = True
    Else
        Sheets("WorkArea").CommandButton1.Enabled = False
    End If
End Sub

If I have to disable all Command Button upon opening of a worksheet, it tried this but does not work, Under which event should i write this. Private Sub Worksheet_Activate() CommandButton1.Enabled = False CommandButton2.Enabled = False CommandButton3.Enabled = False End Sub — Mohammad Haneef Ahmad, Nov 14 '20 at 07:37
You should post this as a new question - it might need an in-depth answer, such as have the CommandButton's been loaded yet. — JMP, Nov 14 '20 at 08:18

VBasic2008 · Answer 2 · 2020-11-14T14:26:34.143

1

A Worksheet Change

This solution will not work if the critical range contains formulas.
To count the number of cells that are not empty you can use the WorksheetFunction.CountA method.
Usually you don't want this code to run when there are changes outside of the range, so you will restrict the code to the range with the Application.Intersect method. You don't have to enable or disable the command button on each change since obviously the code will run on each change.
Since this is all happening in worksheet "WorkArea", there is no need to refer to it by its name i.e. you can safely use Range(rngAddress) and CommandButton1 instead of ThisWorkbook.Worksheets("WorkArea").Range(rngAddress) and ThisWorkbook.Worksheets("WorkArea").CommandButton1 respectively.

The Code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Const rngAddress As String = "S11:T12"
    Dim rng As Range
    Set rng = Range(rngAddress)
    If Not Intersect(Target, rng) Is Nothing Then
        If WorksheetFunction.CountA(rng) = rng.Cells.Count Then
            CommandButton1.Enabled = True
        Else
            CommandButton1.Enabled = False
        End If
    End If
End Sub

edited Nov 14 '20 at 14:26

answered Nov 14 '20 at 09:27

VBasic2008

44,888
5
17
28

1

Resources should be used according to the circumstances, this procedure would fail to update the `CommandButton` when the `Target Range` is populated as a result of a formula, therefore in this case the `Intersect` validation should not be included. – EEM Nov 14 '20 at 12:41
1

That's what the `Change Event` is all about: only when manually a value is changed it gets triggered. So `Intersect` or not, the procedure will fail if the cells contain formulas. The `Target` range cannot contain a formula. But a valid point could be made if a value would manually be changed anywhere in the worksheet due to which the `Calculate Event` would change a value in the range and then the `Change Event` could still enable/disable the `Command Button`, but only without `Intersect`. – VBasic2008 Nov 14 '20 at 13:05
1

By `Target Range` I'm referring to the Range `S11:T12` not the `Target Range` of the Event procedure, apologies for the confusion. Test a situation where the Range `S11:T12`is updated by a formula, i.e. the formula is triggered by a change in cell `C3` then the Range `S11:T12` is populated "or not" by the formula, the `intersect` is nothing and the `Button` is not updated. – EEM Nov 14 '20 at 13:13
1

@EEM: Why I won't change the code in my post: OP wrote *until all 4 cells are populated*. If there were formulas, the cells would have already been populated. Another thing is that if there were formulas, then `CountA` wouldn't work either, because it would always be `4`, while `CountBlank` would work, since it considers cells evaluating to "" as blank. – VBasic2008 Nov 14 '20 at 14:25
1

I'm not asking to change your answer, just telling a situation in which it will not work, i.e.: formula like this `=IF($Q$9,"X",TEXT(,))` not always populates a cell as it may return `BLANK`. over and out. – EEM Nov 14 '20 at 14:36

Enabling the Command Button when 4 Cells are not Empty

2 Answers2

A Worksheet Change