VBA: How to automatically trigger macro without a button

Question

I have made a macro that auto fill the formula on sheet1 whenever the row number of sheet2 is changed.

Is it possible to trigger it automatically without a button when i have any update on sheet2?

Sub Autofill()
Dim sg As Sheets
Dim Row As Long
Dim fillRow As Integer

Application.EnableEvents = False
Row = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
fillRow = Row - 1

Sheets("Sheet1").Select
Range("A1:E1").Select
Selection.Autofill Destination:=Range("A1:E" & fillRow), Type:=xlFillDefault

Application.EnableEvents = True

End Sub

https://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change — Peter Majko, Aug 12 '17 at 16:01
Do your research before posting question please :) it was a first link on google when I put excel vba macro event — Peter Majko, Aug 12 '17 at 16:02
I tried this but it doesnt work in this case, as i usually change the cells value in sheet2 i just add more and more rows to it... :( — Kinghin245, Aug 12 '17 at 16:20
yes it is possible. look up excel vba application and sheet event handling. here is the mdsn https://msdn.microsoft.com/en-us/library/office/hh211482(v=office.14).aspx — ThatGuy, Aug 12 '17 at 19:31
@Kinghin245, your comment (i tried....), makes me think that you want to do more than what you asked in your question. you are talking about changing cells and talking about adding rows. is there more info that should be in your question??? — jsotola, Aug 13 '17 at 01:19

score 1 · Answer 1 · answered Aug 12 '17 at 16:11

1

You could try to create a sub like following:

Paste the following code. And change: 1) "D4" with your cells you want to "monitor" 2) Paste your macro in the line "Do things"

The problem is, your code is run everytime the focus is changed to another cell. But you could also use Worksheet_BeforeDoubleclick if this is enough. Then every time you clicke twice the code will run

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
    If Not Intersect(Target, Range("D4")) Is Nothing Then
        'Do things
    End If
End If

End Sub

answered Aug 12 '17 at 16:11

Marco

328
4
14

the problem is that i wont change any values for previous input, i only add more to the rows, so monitoring a cell doesnt work for me :( – Kinghin245 Aug 12 '17 at 16:22
Luckely you can monitor bigger Ranges. For example you could monitor Range("D:D") (the whole column D) or even bigger areas like Range("A:Z"). – Marco Aug 12 '17 at 17:30
1

`Worksheet_SelectionChange` is cool but dangerous. It is easy to crash excel if events are triggered. I propose to call `Application.EnableEvents = false` before doing anything and reset the event before exit. – John Alexiou Mar 15 '20 at 20:35

score 0 · Answer 2 · answered Aug 12 '17 at 16:32

Right-click on the sheet tab at the bottom of the scree, click 'view code' then insert this following code in there.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Row As Long
Dim fillRow As Integer

This next line will exit the code if column A is not what is being changed on the sheet. Delete it if you want the code to be triggered by any change on any cell of the sheet.

if InRange(Target,Worksheets("Sheet2").range("A:A") = false then exit sub
Row = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
fillRow = Row - 1

Sheets("Sheet1").Select
Range("A1:E1").Select
Selection.Autofill Destination:=Range("A1:E" & fillRow), Type:=xlFillDefault

End Sub

Function InRange(Range1 As Range, Range2 As Range) As Boolean
InRange = Not (Application.Intersect(Range1, Range2) Is Nothing)
End Function

score -1 · Answer 3 · answered Aug 12 '17 at 16:17

use a Worksheet.SelectionChange-Event.

in the Worksheet-VBA for sheet2 add:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Call Autofill() End Sub

(This will be triggered if cell is changed, even if user does not leave the row, so check parameter Target.)

VBA: How to automatically trigger macro without a button

3 Answers3