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

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
– faceplant207, Commented Aug 26, 2021 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
– FaneDuru, Commented Aug 26, 2021 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
– faceplant207, Commented Aug 26, 2021 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
– FaneDuru, Commented Aug 26, 2021 at 17:54

FaneDuru · Accepted Answer · 2021-08-27 16:33:53Z

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, 2021 at 16:33

answered Aug 26, 2021 at 18:29

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

faceplant207 Over a year ago

I will test in an hour after work, thanks.

faceplant207 Over a year ago

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?

Toddleson Over a year ago

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 <> ""

FaneDuru Over a year ago

@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 Over a year ago

@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!

|

VBasic2008 · Accepted Answer · 2021-08-27 12:25:53Z

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.

Collectives™ on Stack Overflow

How to automatically run VBA macro on cell change

2 Answers 2

10 Comments

Increment On Cell Change

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Increment On Cell Change

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related