excel masters

K

killATwill

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.
 
You must log in or register to reply here.
Back
Top