CODEWITHSUNDEEP
excelvbaeventsevent-handling
Mohammad Haneef Ahmad
Mohammad Haneef Ahmad

Reputation: 31

Enabling the Command Button when 4 Cells are not Empty

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

Upvotes: 2

Views: 594

Answers (2)

VBasic2008
VBasic2008

Reputation: 54983

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

Upvotes: 1

JMP
JMP

Reputation: 4467

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

Upvotes: 2

Related Questions