Make a macro run when data entered in a cell [Solved/Closed]

SandsB - Feb 20, 2009 at 11:52 AM - Latest reply:  Rajiv
- Nov 15, 2017 at 12:38 PM
Hello,

I have a macro that runs when the user clicks on a button. Instead, I want the macro to run when Cell D10 is populated. The data for D10 comes from a Data Validation drop down, if that matters. This should be easy but I can't figure it out.
See more 

36 replies

Best answer
WutUp WutUp - Feb 23, 2009 at 04:10 PM
45
Thank you
You can keep your macro in module one. You must use the worksheet change event in the worksheet itself, not a module.
Let me explain.

I will provide a file to go along with the explanation. In the worksheet, range A5:A25 is fill with numbers.
If you enter a numeric value in cell D10, range G5:G10 will copy what is in A5:A25. If you delete the value in D10, then the contents of range G5:G10 will be deleted.

http://www.4shared.com/file/89145449/43c8c38/Change_Value_of_D10.html


Whatever your original code was to run your macro, put that back in it's original form.

Since you are using Excel 2007, this is what you do.

1) Click on the Developer tab.
2) Click on the Visual Basic icon.
3) On the left pane window, double click the sheet where you need your code to run.
4) Now, at the top of the code window you will see... (General) with a drop down, and (Declarations) with a drop down.
5) Click the drop down by (General) and select Worksheet.
6) Now in the code window you will see.... Private Sub Worksheet_SelectionChange(ByVal Target As Range)
7) Remove the word "Selection". You want to remove "Selection" because that means when you click on a cell in the worksheet something will happen. You do not want that, you want to enter a value in D10. It should now read...Private Sub Worksheet_Change(ByVal Target As Range)
8) Now this is where you want the code...

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$D$10" Then

Call MyMacro

End If

End Sub
9) Meaning, when you change the value in D10 then the worksheet change event will "Call" your macro.

Thank you, WutUp WutUp 45

Something to say? Add comment

CCM has helped 1914 users this month

how do I get my description boxes to run on to the next sheet in excel?
Gud Man Thanks
God bless you!!!!!!!
awesome description and work too.. thanks pal
9
Thank you
thanks for this help,

but I need a code that one item entered in sheet1 pasted or moved to sheet2 row 1, then entering the next item in sheet1 should go to sheet2 row 2, hence forth.

Can you help me out.
BashirYazbik 58 Posts Monday November 9, 2015Registration date December 6, 2015 Last seen - Nov 9, 2015 at 11:10 PM
Hi Kishore,

I am trying to do the same worksheet. Do you have any solution?
Thank you.
6
Thank you
Hi folks

Just to pass on a little learning - which cost me an entire day! In 2007 I was really struggling to make worksheet change procedures (which had run perfectly previously in other files) run in a file. It just seemed to do nothing.

What I eventually found was that somehow the Application.EnableEvents property had been set to FALSE (I have absolutely no idea how) but a one line macro that set it to TRUE solved the problem. Now all I have to do is find where the code is that is doing it!

I probably should close Excel more often as it could be anything in the last couple of weeks that did it
Thank you sooooooo much!
I want to buy you a beer for this!
You saved my sanity. Thank you!
5
Thank you
Hi I am very new to VBA. Because of this thread I do sort of know what you mean but the code doesnt seem to be working for me.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$8" Then
Call PVValue
Call OrderData

End If

End Sub

the sub function PVValue and OrderData are in a modul, and otherwise work absolutely fine. what could be wrong??

thanks
Bhumi
Did you put the code in the worksheet or create another module?
If you did, are you putting a value in A8, or are you wanting to click on it?
macroHelp > WutUp WutUp - Jan 21, 2010 at 08:12 AM
Hello. I need help using creating a macro that finds corresponding/matching values. I have 2 worksheets (Attendance & Summary). In Summary worksheet, I have the column A with the names of my students. In column B. I need to get their corresponding number of absences. The data for their number of absences is stored in Names worksheet. Please Help! Im new with VMB and I've been working on this for 2 weeks now.
I cant just copy paste because I want this macro to run in "Summary" worksheet everytime a new data is enetered in "Names" sheet. I tried to us vlookup but it still wouldnt work. Thanks!!!
The worksheets look as follows:

"Names" worksheet:
Name Absences Late
student1 1 3
student2 1 0
student3 3 1

"Summary" worksheet:
Name Absences
student1 1
student2 1
student3 3
hazel_1028@hotmail.com - Jan 20, 2010 at 03:08 AM
Hello. I need help using creating a macro that finds corresponding/matching values. I have 2 worksheets (Attendance & Summary). In Summary worksheet, I have the column A with the names of my students. In column B. I need to get their corresponding number of absences. The data for their number of absences is stored in Names worksheet. Please Help! Im new with VMB and I've been working on this for 2 weeks now.
I cant just copy paste because I want this macro to run in "Summary" worksheet everytime a new data is enetered in "Names" sheet. I tried to us vlookup but it still wouldnt work. Thanks!!!
The worksheets look as follows:

"Names" worksheet:
Name Absences Late
student1 1 3
student2 1 0
student3 3 1

"Summary" worksheet:
Name Absences
student1 1
student2 1
student3 3
Hi I have created few views:

i have columns like Nov-07, Dec-07, Jan-08, Feb-08 till Dec15
First view is just showing months in year 2007 and the name of the view is 2007
and another view named 2008, which is showing all the months in 2008 and hiding the remaining, now I want if the user put 2007 in a particular cel than the view 2007 get activated and so on like if 2009 ten it should activate the view named 2009, pls help and I have many sheets whic has the same format and want the same views for all the sheets and same functionality.

Many thanks in advance if possible or not
Try using brackets after the name of macro e.g.
Call PVValue()
rizvisa1 4482 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 19, 2010 at 10:21 PM
5
Thank you
see these parts of thread.

http://ccm.net/...
and
http://ccm.net/...
4
Thank you
I tried this but nothing happens. What am I doing wrong here? I entered it in Module1. I'm using Excel 2007 if it matters.

Private Sub GetNotes(ByVal Target As Range)
If Target.Address = "$D$10" Then
If Range("D10").Value = "MyText" Then
Range("C15").Value = "This is what happens when D10 is equal to the text I want."
End If
If Range("D10").Value = "" Then
Range("C15").Value = ""
End If
End If
End Sub
Hey WutUp WutUp

I almost have a same problem:

I need to validate columns for alphanumeric and email format.below is the sequence of my code:

Created two Subs in "Module2" like below :

Public Sub alpha(ByVal Target As Range)
cellNeedValidation = "A2:A10"
'if more cells need validation, on right side of "cellNeedValidation =" put "A1,C3,D7"
If Not Intersect(Target, Range(cellNeedValidation)) Is Nothing Then
For g = 1 To Len(Target.Value)
testchar = Asc(Mid(Target.Value, g, 1))
flg = 0
If testchar > 47 And testchar < 58 Then
flg = 1
ElseIf testchar > 64 And testchar < 91 Then
flg = 1
ElseIf testchar > 96 And testchar < 123 Then
flg = 1
End If
If flg = 0 Then
MsgBox "Your entry contains invalid character." & Chr(13) & _
"Character allowed are a-z, A-Z & 0-9.", vbOKOnly, "Invalid Character"
Target.Select
Application.SendKeys "{f2}+^{home}"
flg = 0
Exit For
End If
Next
End If
End Sub

Public Sub email(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B2:B10")) Is Nothing Then
Dim RE As Object
Set RE = CreateObject("vbscript.REgExp")
RE.Pattern = "^[a-zA-Z0-9\._-]+@([a-zA-Z0-9_-]+\.)+([a-zA-Z]{2,3})$"
If RE.test(Target.Value) = False Then

MsgBox "Invalid email." & Chr(13) & _
"Character allowed are a-z, A-Z & 0-9.", vbOKOnly, "Invalid Character"
Target.Select
Application.SendKeys "{f2}+^{home}"
End If
Set RE = Nothing
End If
End Sub

==========================================
Created a macro in "Module1" like below and calling Subs in macro :

Sub Macroemailalpha()
'
' Macroemailalpha Macro
' Macro recorded 4/20/2010 by aaig
''
Call alpha(Target)
Call email(Target)

End Sub

=========================================
Calling Macro in Sheet1 like below :

Private Sub Worksheet_Change(ByVal Target As Range)
Call Macroemailalpha
End Sub


But not working
Please help anyone.
SandsB

Hi,

I encounter the same problem today but, as I wanted to download the file WutUp posted with the explanation I could not.

Would you kindly send me that file regardind your first doubt ?

Thanks in advance.
4
Thank you
Hi May be this will helpfull to you, try below one

Private Sub Worksheet_Change(ByVal Target As Range)

Set MyPlage = Range("G4:M11")
For Each Cell In MyPlage


If Cell.Value = 1 Then
range("G4:M11").select
With Selection.Interior
.Color=255
End with
elseIf Cell.Value = 2 Then
With Selection.Interior
.Color=46
End with
End If

Next
End Sub
WutUp WutUp - Feb 20, 2009 at 02:13 PM
3
Thank you
You could save your current macro from the button into a module, and then make a code for a worksheet change event.

Something like......

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$10" Then

Call MyMacro

End If
what about if you want to set it to a specific value, not just to change
rizvisa1 4482 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 4, 2010 at 07:04 AM
MM. are you to referring to
Private Sub Worksheet_Change(ByVal Target As Range)
...
End If
when you say "what about if you want to set it to a specific value, not just to change". If that is the case is an event driven routine. This event is triggered if a value of a cell is changed manually.
3
Thank you
Hi,

Thank you for this thread, It has been a great help to me. I have a little extension of the problem though.
I have a spreadsheet which calls on data from SQL which my I.T guy set up, and it then populates information on sheets which have been created from running a previous macro. (If that doesn't make sense, let me know).
Then I actually want the function of being able to call the macro on these new sheets however it won't copy the call function into the new sheets.
I currently have a macro button on the original "template" doc, which is copied to the new sheets, however it'd be nice if I could get rid of these buttons and have the macro run on a change.
Is there any way of doing this or am I asking too much of excel??
Thanks
3
Thank you
Instead of the Button reference give cell reference in your coding, this will work.

Cheers,
Kishore.
2
Thank you
I am trying to use VB to change background colors in Excel. I think I have the right code, but I don't know how to correctly begin or end it. Should I not use the first line? Should I not use MyPlage (got that from a website)? When I enter in the code and hit run, I get a box asking me to type in the macro name. Can anyone help?


Private Sub Worksheet_Change(ByVal Target As Range)

Set MyPlage = Range("G4:M11")
For Each Cell In MyPlage

If Cell.Value = 1 Then
Cell.Interior.ColorIndex = 5
End If

If Cell.Value = 2 Then
Cell.Interior.ColorIndex = 46
End If

Next
End Sub