2

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
Improve this question
4
  • Either validate the cell values one cell at a time or concatenate the all the cell values then do the validation. Commented Nov 14, 2020 at 5:18
  • But in what event do i need to write the code Commented Nov 14, 2020 at 5:33
  • 1
    Suggest to use the Worksheet_Change event. It will be triggered for every change into the worksheet. Commented Nov 14, 2020 at 5:37
  • 1
    I agree with @EEM use Worksheet_Change Commented Nov 14, 2020 at 6:19

2 Answers 2

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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
You should post this as a new question - it might need an in-depth answer, such as have the CommandButton's been loaded yet.
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
Improve this answer

5 Comments

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.
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.
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:T12is 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: 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.
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.