Getting a macro to run automatically when a cell value changes

sschluet

New Member
Joined
Apr 5, 2013
Messages
12
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?
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

WhoCanDo

Board Regular
Joined
Dec 18, 2007
Messages
176
Try this, you need to add this macro to the page it works on (ie Sheet1 (Sheet1) and not "Module 1")

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

then revised your code like this:

Code:
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

New Member
Joined
Apr 5, 2013
Messages
12
Thanks for the suggestion but it didn't work for me. Now it's manual and it also gives me your popup box.
 

WhoCanDo

Board Regular
Joined
Dec 18, 2007
Messages
176
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

Board Regular
Joined
Dec 18, 2007
Messages
176

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Dec 18, 2007
Messages
176

ADVERTISEMENT

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.
 
Last edited:

WhoCanDo

Board Regular
Joined
Dec 18, 2007
Messages
176
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

New Member
Joined
Apr 5, 2013
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top