i'm trying to write some simple code that runs a macro on an event change. i'm starting with something simple, but i can get anything that starts with a private sub to work, such as... Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell.Text = "N" Then MsgBox "You must enter a reason code!" End If End Sub excel is doing nothing. do i need to modify excel's settings or add some code to my vba module? thanks
Not all things that change a worksheet will result in the worksheet_change event being triggered. Check the help text for the change event from the object browser in the IDE for details. Also check Tools - Macros - Security settings.
You need to put the code in the private module for the worksheet in question. ie. Right click on the worksheet name at the bottom of your Excel workbook and select "View Code", then paste the code you have already posted and it should work.
thanks much. i realized the bit about the worsheet, rather than in a vba module. the dde is so much tighter!! if anyone wants code for the IB excel dde that triggers a macro when a cell value changes through a formula, just substitute the bingo message box below with your value... Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Target.Cells.Count > 1 Then Exit Sub On Error GoTo EndMacro If Not Target.HasFormula Then Set rng = Target.Dependents If Not Intersect(Range("A1"), rng) Is Nothing Then If Range("A1").Value > 200 Then MsgBox ("bingo") End If End If EndMacro: End If End Sub i will probably be using this to trigger auto orders.
This would do the same thing but is a little simpler.... Private Sub Worksheet_Calculate() Application.EnableEvents = False MsgBox ("bingo") Application.EnableEvents = True End Sub The Application.EnableEvents doesn't do anything in this instance, but stops the macro feeding back on itself if the code within it changes anything on the worksheet.