Pausing a macro repeatedly for data entry

Question

Sub COLUMN_C()
'
' COLUMN_C Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
ActiveCell.FormulaR1C1 = "P"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "25"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "2"
ActiveCell.Offset(1, -5).Range("A1").Select
End Sub

I need this macro to pause for "data" and take me to the next cell down so I can start all over again and hopefully loop 'COLUMN_C' so I can start from, say, C8 and just keep going down the column until the job's done or my fingers grow numb. Love Excel but know very little about editing macros and making them work harder!

I looked at several online suggestions and could not understand any of them. It's a simple macro doing a very important job and I'm hoping that an expert can show me how to put pauses in place of "P" and "25" and "2" and speed up the work I am doing which right now is rewarding but tedious.

Answer 1

This code

Sub selectCells()
    Dim r As Long
    Dim c As Long
    Do
        r = ActiveCell.Row
        c = ActiveCell.Column
        Call waitForInput(ActiveCell.Address)
        If Cells(r, c).Value = "stop" Then Exit Sub
        Cells(r, c + 3).Select
        Call waitForInput(ActiveCell.Address)
        Cells(r, c + 5).Select
        Call waitForInput(ActiveCell.Address)
        Cells(r + 1, c).Select
    Loop
End Sub

Sub waitForInput(start As String)
    Do While ActiveCell.Address = start
        DoEvents
    Loop
End Sub

will

1. wait until you enter something in the active cell
2. wait again until you enter something in the cell 3 cells to the right of that
3. wait again until you enter something in the cell 2 cells to the right of that

It will then move to the next row (immediately under the originally active cell of the previous row) and repeat the process (unless you enter stop in that first cell).

Answer 2

Basically, you cannot pause a macro until something happens. There is one exception, but it involves starting a form (either a build-in form like msgBox or inputBox or a fileDialog, or a user form).

You solve such issues usually in a different way: You react on "events", do your job (in your case filling some other cells) and that's it.

In your case, the event is that the user (=you) changed something in the worksheet. Every time something is entered, Excel looks for a routine that handles this event, and if it is present, it is called automatically. The event that reacts on user input on a sheet is called Change. Excel finds this routine only if

• it is placed in the correct module
• it has the correct name
• it has the correct parameter definition.

Now that sounds more complicated as it is because the VBA editor will help you. First step is to open the correct module - for a worksheet change event, this is the worksheet module for that sheet (this is created automatically). This is how it looks for sheet1 in the project explorer:

Now at the top above the window where you enter your code, you select Worksheet on the left side and Change on the right, and voilà, the VBA editor will create an (empty) event handler routine for you

The name of an event handler follows always the naming convention where + underscore + what. where is usually either Worksheet or Workbook and what describes the event: Change, Activate, SelectionChange and so on.

Different event routines get different parameter. The change event routine gets a parameter target that tells your code which cell was modified (note that target can contain more than one cell, eg when you copy&paste data into more than one cell, but we will ignore that for the moment).

Now what do you want to do? Whenever something was entered in a cell of column C, you want to write 25 to the cell in column F of the same row and 2 in column H, and after that, jump to the next row.

So your first attempt could look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub  ' More than one cell entered - ignore.
    If Target.Column <> 3 Then Exit Sub ' Not in Column C
    
    If IsEmpty(Target.Offset(0, 3)) Then Target.Offset(0, 3) = 25
    If IsEmpty(Target.Offset(0, 5)) Then Target.Offset(0, 5) = 2

    Target.Offset(1, 0).Select
End Sub

Note that this routine ends, it will not pause. Instead, when the user enters the next value, it is called again (and again and again).

Small addition that is important to know: When you modify data within the change event routine, the event routine is triggered another time. This is not a big deal for our code here, but might result in a cascading stack of calls of the event routine (until, in the worst case, you get the infamous "Stack Overflow" error).
To prevent this, you usually disable events at the beginning of the code and enable them when done. To be sure that events are always enabled, even if an error occurred during code execution, use an error handler:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub  ' More than one cell entered - ignore.
    If Target.Column <> 3 Then Exit Sub ' Not in Column C
    
    On Error GoTo Change_exit
    Application.EnableEvents = False
    If IsEmpty(Target.Offset(0, 3)) Then Target.Offset(0, 3) = 25
    If IsEmpty(Target.Offset(0, 5)) Then Target.Offset(0, 5) = 2

    Target.Offset(1, 0).Select
Change_exit:
    Application.EnableEvents = True
End Sub

One remark: As you probably already noticed, there is no need to Select a cell before you can write something into it - and you never should do it.