How to automatically run VBA macro on cell change

Question

I have searched extensively for a solution to this but nothing I've tried works automatically. The goal is that anytime text is manually entered in column C, the macro will find the last number used in column CG, increment by 1 if less than 6 and then enter that value on the active row. This works when run manually, but I cannot figure out how to automatically trigger when data is entered in column C.


Sub Counting()

Dim rng As Range
Dim text As String
Dim counterNumber As Integer
Dim counter As Range
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1")
Set rng = ws.Cells(ActiveCell.Row, 3)

text = ""

If rng <> text Then
    Set counter = rng
    Set counter = counter.Offset(-1, 82)    'Finds the last value entered in the Counter column
    counterNumber = counter                 'Temporary storage for counter number
    If counterNumber = 6 Then               'Restarts counting loop
        counterNumber = 0
    End If
    counterNumber = counterNumber + 1       'Increase counter number by 1
    Set counter = counter.Offset(1, 0)      'Returns to the active row
    counter = counterNumber                 'Inputs the updated counter number
    
End If

End Sub

It is a [Worksheet_Change](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.change) event — Scott Craner, Aug 26 '21 at 17:18
I did see those examples, but they all used Intersect and I'm not really sure how that works. I'm fairly new to VBA and am just trying to add some QoL improvements to a file my team uses. — faceplant207, Aug 26 '21 at 17:40
What does "enter that value on the active row" mean? To enter the value in all the row? You say "increment by 1 if less than 6". What to be done if it is 6 or larger? Can the cell be changed anywhere on the column, even much below the last empty cell? Does "manually entered in column C" means only adding values to the last empty cell? Should a change in the existing C:C column cells be triggered and treat as you stated? — FaneDuru, Aug 26 '21 at 17:43
The counter has to move up a row to find the last number, then after it stores that value in counterNumber, the position is shifted down one row which is back on the active row the user is on. If the value is 6 or larger, then counterNumber is reset to 0. Manually entered will always be on the last empty row, but doesn't necessarily have to be. I would expect any change to column C would trigger the macro, whether it is a blank or an existing row. — faceplant207, Aug 26 '21 at 17:50
How **exactly** to be treated a modified cell of C:C column. Please, try building an example. Should also the code take in consideration the last value in CG:CG, or the value on the modified cell row? — FaneDuru, Aug 26 '21 at 17:54
In conclusion, **the last entered number in column C:C does not matter at all**. It should only trigger the event and **the efect should be only filling of the first empty row of CG:CG column using the algorithm able to alocate numbers from 1 to 6**. Is this understanding correct? — FaneDuru, Aug 26 '21 at 18:06
Yes. C:C is a text string, but as you mentioned, the contents are irrelevant. It only matters if there is a change which will fill out the first empty row of CG:CG. — faceplant207, Aug 26 '21 at 18:09
So, the last empty row, not something connected to the changed cell row. Your code uses Offset(-1, 82), which works well only for adding new records to the last empty row in C:C. Is this understanding correct, too? — FaneDuru, Aug 26 '21 at 18:14
Is it possible that more cells to be pasted at once in column C:C? If yes, how to be treated such a situation? To increment once, or as many time as cells have been copied? — FaneDuru, Aug 26 '21 at 18:16
If the code will count any change in the existing cells, the last empty row in CG:CG should be below the last row in C:C. Do you understand what I mean? — FaneDuru, Aug 26 '21 at 18:19
Only one value at a time would be entered into C:C, not multiple rows simultaneously. I said the first empty row earlier in my comment, but that isn't exactly correct. Any change in C:C regardless of it being the first empty row or not should trigger the corresponding adjustment to CG:CG — faceplant207, Aug 26 '21 at 18:21
https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure — Siddharth Rout, Aug 26 '21 at 19:02

FaneDuru · Accepted Answer · 2021-08-27T16:33:53.710

1

Please, copy next code in the sheet to be processed code module. To do that, copy the code, right click on the sheet name, choose View Code and paste:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 3 Then                      'treat only changes in column C:C
    Dim counterNumber As Integer, counter As Range, lastR As Long
    
    If Target.Value <> "" Then
        Set counter = Me.Range("CG" & Target.row - 1) 'the counter to be updated
        counterNumber = counter.Value         'Temporary storage for counter number
        If counterNumber = 6 Then               'Restarts counting loop
            counterNumber = 0
        End If
        counterNumber = counterNumber + 1          'Increment counter by 1
        counter.Offset(1, 0).Value = counterNumber  'Inputs the incremented number
    End If
 End If
End Sub

edited Aug 27 '21 at 16:33

answered Aug 26 '21 at 18:29

FaneDuru

38,298
4
19
27

I will test in an hour after work, thanks. – faceplant207 Aug 26 '21 at 18:38
is there a way to not go from the very bottom up? This solution does not account for editing a row that isn't the first empty row. Is there a way to incorporate ActiveCell.Row? – faceplant207 Aug 26 '21 at 19:47
You should check `Target.Cells.Count`. Otherwise `Target.Value <> ""` will give you an error when `Target.Value` is an array instead of a single Variant. You could resolve it by specifying `Target.Cells(1).Value <> ""` – Toddleson Aug 26 '21 at 19:55
@faceplant207 I'm afraid I do not understand what you mean by "not go from the very bottom up". Then what "incorporate ActiveCell.Row" should mean? In what to incorporate it? Now it is late in my country and I will close my laptop. Please, try clarifying the issues you are talking about and I will try adapting the code if I will be able to understand what you need. I told you that the CG column last row will be below the C:C one, **if the event will be triggered by any change of the existing values**. – FaneDuru Aug 26 '21 at 20:09
@Toddleson Yes, I will make the event code stopping if multiple cells are changed. I had that in mind, but forgot to do it. Thanks! – FaneDuru Aug 26 '21 at 20:11
@faceplant207 Didn't you find some time to clarify the issues I asked for? The `ActiveCell` is `Target`, but what to do with it if you confirmed that its value does not matter for the code to update **"the last empty cell in CG"**. Do you really know what you need? If yes, and you are not able to describe it, we cannot help you... – FaneDuru Aug 27 '21 at 07:24
@FaneDuru The problem with Me.Range("CG" & Me.rows.count).End(xlUp) is that if the first empty row is 50, but I make a change to C10 then CG50 is going to be updated and not CG10. – faceplant207 Aug 27 '21 at 15:38
@FaneDuru I apologize for the delay in responding. We have a hurricane heading toward us and I've been making preparations. – faceplant207 Aug 27 '21 at 15:48
@faceplant207 I did not understand that from our previous discussion. Anyhow, I will adapt the code to update the corresponding CG row, using the above row as reference. Is this understanding correct? – FaneDuru Aug 27 '21 at 16:27
@faceplant207 Please, test the updated code. It should do what (I understood) you need. – FaneDuru Aug 27 '21 at 16:34

score 0 · Answer 2 · answered Aug 27 '21 at 12:25

Increment On Cell Change

Standard Module e.g. Module1

Option Explicit

Sub IncrementInColumn( _
        ByVal Target As Range)
    
    ' Source
    Const sCol As String = "C"
    Const sNotCriteria As String = ""
    ' Destination
    Const dCol As String = "CG"
    Const dCounterMin As Long = 1
    Const dCounterMax As Long = 6
    Const dNotCriteria As String = "" '***
    ' Or maybe:
    'Const dNotCriteria As Long = 0
    
    Dim ws As Worksheet: Set ws = Target.Worksheet
    Dim srg As Range: Set srg = Intersect(Target, ws.Columns(sCol))
    If srg Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False ' to stop retriggering the event procedure
    On Error GoTo ClearError ' to enable the settings if something goes wrong
    
    Dim sCell As Range
    Dim dCell As Range
    Dim dValue As Variant
    For Each sCell In srg.Cells
        Set dCell = sCell.EntireRow.Columns(dCol)
        If CStr(sCell.Value) <> sNotCriteria Then
            dValue = dCell.Value
            If IsNumeric(dValue) Then
                If dValue >= dCounterMax Or dValue < dCounterMin Then
                    dCell.Value = dCounterMin
                Else
                    dCell.Value = Int(dValue) + 1
                End If
            Else
                dCell.Value = dCounterMin
            End If
        Else
            dCell.Value = dNotCriteria '***
        End If
    Next sCell
    
SafeExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    Exit Sub

ClearError:
    Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume SafeExit
    
End Sub

' Just a Manual Test
Sub IncrementColumnTEST()
    If Not TypeName(Selection) = "Range" Then Exit Sub
    IncrementInColumn Selection
End Sub

Sheet Module e.g. Sheet1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    IncrementInColumn Target
End Sub

@VBaisc2008 I apologize for the delay in testing this answer and providing feedback. We have a hurricane heading toward us and I've been making preparations. I will provide comments as soon as I can properly test. — faceplant207, Aug 27 '21 at 15:49

How to automatically run VBA macro on cell change

2 Answers2

Increment On Cell Change