Getting a macro to run automatically when a cell value changes

[sschluet]

I have some code I would like to execute automatically any time the value in C1 is changed:

Sub common2()
Dim sh As Worksheet, lr As Long, rng As Range, Brng As Range, Crng As Range
Set sh = Sheets(6) 'Edit sheet name

If sh.Range("C1").Value = "" Then
sh.Range("I2:I6").ClearContents
Else

lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A1:A" & lr)
For Each C In rng
Set Brng = sh.Range("B:B").Find(C.Value, LookIn:=xlValues)
Set Crng = sh.Range("C:C").Find(C.Value, LookIn:=xlValues)
If Not Brng Is Nothing And Not Crng Is Nothing Then
sh.Cells(Rows.Count, 9).End(xlUp)(2) = C.Value
End If
Set Brng = Nothing
Set Crng = Nothing
Next

End If

End Sub

I've tried adding

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("C1")) Is Nothing Then

Set sh = Sheets(6) 'Edit sheet name
If sh.Range("C1").Value = "" Then
sh.Range("I2:I6").ClearContents
Exit Sub
Else
Call Sheet4.common2
End If
End Sub

but no matter how many examples I've looked at I still seem to be missing something because it never runs automatically. What am I doing wrong here?

 

[WhoCanDo]

Try this, you need to add this macro to the page it works on (ie Sheet1 (Sheet1) and not "Module 1")

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
    If (ActiveCell.Address = "$C$9") Then _
        Call common2
End Sub

then revised your code like this:

Sub common2()
Dim sh As Worksheet, lr As Long, rng As Range, Brng As Range, Crng As Range
Set sh = Sheets(6) 'Edit sheet name

    Range("C9").Value = InputBox("What's up:") ' added vba code
    If sh.Range("C1").Value = "" Then
        sh.Range("I2:I6").ClearContents
    Else

This works by detecting the activated cell is "C9" but you will have to enter data via the macro I think.

Regards

 

[sschluet]

Thanks for the suggestion but it didn't work for me. Now it's manual and it also gives me your popup box.

 

[WhoCanDo]

So do you want a macro to watch a cell to see if it changes automatically (via a formula or something) and then run another macro?

 

[WhoCanDo]

This should do it:

Add this to "ThisWorkbook":
Private Sub Workbook_Open()
'
Range("A1").Value = "C9"
End Sub

and this to "Sheet1":
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
If (Range("C9").Value <> Range("A1").Value) Then
Range("A1").Value = Range("C9").Value
Call common2
End If
End Sub

and now use your original common2 macro without my changes.

 

[Rick Rothstein]

I have some code I would like to execute automatically any time the value in C1 is changed:

What is in C1... a constant value or a formula (select C1 and then check the Formula Bar to be sure)?

 

[WhoCanDo]

Sorry just re-read your original request.

C9 in my macro is your C1 and A1 is any cell for cross referencing, just change the macro to suit or you may be able to declare a constant in memory.

 

[WhoCanDo]

Got busy at work today so I didn't have my mind on this, sorry.

Put this in "ThisWorkbook":
Private Sub Workbook_Open()
'
Range("A1").Value = Range("C1").Value
End Sub

and this in "Sheet1":
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
If (Range("C1").Value <> Range("A1").Value) Then
Range("A1").Value = Range("C1").Value
Call common2
End If
End Sub

"A1" is a cross-reference record to compare with the value of "C1" so you can make it any cell and change the macro accordingly.

I think you have a formula in "C1" so when it changes then your original common2 macro will run.

but if you are changing "C1" via a macro then since this process relies on detecting a selection change, you may also need to add:
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Cells(ActiveCell.Row - 1, ActiveCell.Column).Select

at the end of your macro to force a selection change.

Regards

 

[sschluet]

What is in C1... a constant value or a formula (select C1 and then check the Formula Bar to be sure)?

C1 has =Searching_problem_AND_equip!$B$26 formula in it. I'm very new to VBA so I used VLOOKUP on another sheet to get the values I need and now I'm compiling things on a hidden sheet to give me the answers I need. Painful, but when you don't know what you're doing you end up with longer, less efficient processes.