Trigger macro automatically on cell value change |Excel|VBA|

Question

Sheet 1 as ENTER VALUE

Sheet 2 as MY VALUE and DATA

Sheet value is referenced from Sheet1 Value using =Sheet1!E4

I want to avoid onclick Button event => as soon as value in sheet 1 changes to 10 then My value in sheet 2 changes to 10 => then taking the value which is changed in sheet 2 the macros should automatically detect the value change as occured then print of Data should happen that many times in column I

Module 1 my macros :

Sub mac()

Dim ws As Worksheet
Dim rDest As Range
Dim lCount As Long
Dim sValue As String

Set ws = ActiveWorkbook.ActiveSheet
Set rDest = ws.Range("I2")

With ws.Range(rDest, ws.Cells(ws.Rows.Count, rDest.Column).End(xlUp))
    If .Row >= rDest.Row Then .ClearContents
End With

lCount = Val(ws.Range("E4").Value)
sValue = ws.Range("E8").Value

If lCount > 0 Then rDest.Resize(lCount) = sValue

End Sub

Sheet 2 : Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("E4"), Range(Target.Address)) Is Nothing Then
Call mac
End Sub

output : should be like this below Output Image: https://i.stack.imgur.com/voz7g.png

You've added quite a lot of detail, but not quite clear what you think is not working...? — FAB, May 25 '19 at 08:30
You probably want to use that `Worksheet_Change` event in `Sheet1` instead? And set your worksheet by name in your function `Set ws = ActiveWorkbook.Sheets("Sheet2")` — FAB, May 25 '19 at 08:40
Possible duplicate of [VBA trigger macro on cell value change](https://stackoverflow.com/questions/18500608/vba-trigger-macro-on-cell-value-change) — QuickSilver, May 26 '19 at 08:04

score 2 · Accepted Answer · answered May 25 '19 at 09:01

2

Few things

Your Worksheet_Change procedure should be in Sheet1 code instead of Sheet2 - least from the way you phrased your question.
Also be explicit with your Worksheet declarations (eg. Sheets("Sheet1")), instead of using things like ActiveSheet
In your Intersect, notation of Range(Target.Address) is completely redundant, as Target already is a Range object.

Your code in Sheet1 should be:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Sheets("Sheet1").Range("E4"), Target) Is Nothing Then
       Call mac
    End If
End Sub

And In Module

Private Sub mac()

Dim ws As Worksheet
Dim rDest As Range
Dim lCount As Long
Dim sValue As String

Set ws = ThisWorkbook.Sheets("Sheet2") ' note the explicit declaration
Set rDest = ws.Range("I2")

With ws.Range(rDest, ws.Cells(ws.Rows.Count, rDest.Column).End(xlUp))
    If .Row >= rDest.Row Then .ClearContents
End With

lCount = Val(ws.Range("E4").Value)
sValue = ws.Range("E8").Value

If lCount > 0 Then rDest.Resize(lCount) = sValue

End Sub

And as a personal note, I would beware of using "single-line If syntax" in my opinion it is a bad programming practice and leads to unexpected mistakes and worse code readability.

As a good example as to why, check this question/answer

answered May 25 '19 at 09:01

Samuel Hulla

6,617
7
36
70

Then you have something wrong with your macro procedure, I can't read your mind, but my answer is programatically correct – Samuel Hulla May 25 '19 at 14:45
Thanks Bro Working !!! Little Bit changes i made Sheet 1 Code it should be F5 not sheet 2 Value 'E4' => We need to pass value of sheet 1 – May 25 '19 at 14:47
Well glad it helped. – Samuel Hulla May 25 '19 at 14:50
is it possible to take two values and print parallel like this => https://paste.pics/3eb581ed87184d251d910328537f2973 => it would be really helpful for me – May 25 '19 at 16:40
create a separate question for that – Samuel Hulla May 25 '19 at 16:53
=> i have created a Separate Question please have a look at it => run macros dynamically on value change |VBA|Excel| => https://stackoverflow.com/questions/56311747/run-macros-dynamically-on-value-change-vbaexcel – May 26 '19 at 08:07

Trigger macro automatically on cell value change |Excel|VBA|

1 Answers1