excel masters

Discussion in 'Trading Software' started by killATwill, May 18, 2005.

  1. 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?

  2. 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.
  3. 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.
  4. 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
    End If
    End Sub

    i will probably be using this to trigger auto orders.
  5. 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.
  6. linertine, how would you specify for a particular cell? thanks