Trigger macro once all cells in a range have value

Question

I would like to trigger a macro to run only after all of the cells within a given range have a value. I want to have my input range open without the values entered when the workbook was closed. Currently the code below runs when the workbook is opened.

Sub Page_Setup()

Sheet1.Unprotect
Application.EnableEvents = False
Application.ScreenUpdating = False

Sheet1.Range("Inputs").Style = "40% - Accent2"
Sheet1.Range("Calcs").Style = "40% - Accent3"
Sheet1.Range("C8:C12").Value = "-"
Sheet1.Range("Calcs").Value = "-"
Sheet1.Range("C13").Value = 64
Sheet1.Range("C14:C16").Value = 0

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

I want the macro to run once the user has entered a value in each cell from C8 to C12. Thanks in advance for any help.

Edit:

What I would like is for the macro to run only if the range "C8:C12" have a numeric value. I prefer to clear that range when the workbook is initially opened to avoid showing the values that were entered (and calculated by the macro) when the workbook was closed. The code below runs when the workbook opens up.

Private Sub Workbook_Open()

Application.ScreenUpdating = False

Sheet1.Range("User").Value = Environ("UserName")
Sheet1.Range("Run_Date").Value = Format(Date, "mm/dd/yy")
Application.Run ("Page_Setup")

Application.ScreenUpdating = True

End Sub

Then I would like to trigger a separate macro (Case1) to run once C8:C12 all have numeric values. I have the code below

Private Sub Worksheet_Change(ByVal Target As Range)

Sheet1.Unprotect
Sheet2.Unprotect
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim KeyCells As Range

Set KeyCells = Worksheets("Sheet1").Range("Inputs")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing   Then

    Application.Run ("Case1")

End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

My problem is that Case1 will return an error if the cells do not have a numeric value. Sorry for the confusion and I hope this clarifies my question.

You can't do that directly, but you can emulate it with Worksheet.Change() In the change event, check if the user has just entered a value in C8:C12, and if all of those cells have a value, and if so invoke the macro you want. The Change event acts as a gate keeper. — John Coleman
How would I go about checking if the cells have a numeric value? I have a Worksheet.Change() running that triggers the macro when the range changes. — Derek
Will the values be entered on Sheet1? Same as the PageSetup code? And C8:C12 will be wiped out every time after all the values are entered? — Scott Holtzman
C8:C12 are set to be cleared only when the workbook is opened. The values will all be entered on Sheet1. — Derek
Derek - The code you provided and your question are a bit confusing. If you have worksheet_change event code already, please edit your question with that code and let us know what is not working. The description, code and comments are a bit all over the place. — Scott Holtzman

user3598756 user3598756 · Accepted Answer · 2016-11-30T08:17:35

my understanding is you have to

place the following code in ThisWorkbook code pane:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Application.EnableEvents = False '<--| this to avoid subsequent '.Range("Inputs").ClearContents' trigger 'Worksheet_Change()'
    With Sheet1
        .Range("User").Value = Environ("UserName")
        .Range("Run_Date").Value = Format(Date, "mm/dd/yy")
        .Range("Inputs").ClearContents
    End With
    Application.Run "Page_Setup"
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

place the following code in your Sheet1 code pane:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Range("Inputs") '<--| reference your "inputs" range
        If Not Intersect(.Cells, Target) Is Nothing Then '<--| if change affects referenced range
            If WorksheetFunction.Count(.Cells) = .Cells.Count Then '<--| if referenced range is "full" with numbers
                Application.ScreenUpdating = False
                Application.EnableEvents = False
                Sheet1.Unprotect
                Sheet2.Unprotect
                On Error GoTo ErrHandler '<--| make sure you always exit this event handler properly

                Application.Run "Case1"

ErrHandler:     '<--| restore settings
                Sheet1.Protect '<-- add password if needed
                Sheet2.Protect '<-- add password if needed
                Application.ScreenUpdating = True
                Application.EnableEvents = True
            End If
        End If
    End With
End Sub

Trigger macro once all cells in a range have value

2 Answers