Auto Fire macro

[dixiedean]

Hi All

Is there anyway that when BA loads a new market i can get a macro to run automatically? If i use a formula to pull the time from the market tab and it updates a cell using that ormula how do i get it to fire the macro, if i enter the time manually the macro runs in ie: 14:00 I am using excel 2007

Cheers

D

[negapo]

You can check for the marketid to change and then fire the macro whenever it changes like this:

Code: Select all

Public CurrentMarketID As Long
Private Sub Worksheet_Change(ByVal Target As Range)
    If CurrentMarketID <> Me.Cells(2, 1) Then
        CurrentMarketID = Me.Cells(2, 1)
        MyMacroName
    End If
End Sub


Me.Cells(2, 1) is the location of the MarketID in the sheet, i don't know where it is so you have to change it.

[dixiedean]

You can check for the marketid to change and then fire the macro whenever it changes like this:

Code: Select all

Public CurrentMarketID As Long
Private Sub Worksheet_Change(ByVal Target As Range)
    If CurrentMarketID <> Me.Cells(2, 1) Then
        CurrentMarketID = Me.Cells(2, 1)
        MyMacroName
    End If
End Sub


Me.Cells(2, 1) is the location of the MarketID in the sheet, i don't know where it is so you have to change it.

Thanks mate that triggers a macro but i have run into another problem my macro only runs if i input a time say 14:00 into cell a19 but will only run if cell a5 is formatted as text? I have this in cell a20 =MID(A1,SEARCH("-",A1)+1,6) which will pull out the 14:00 from the event string ie: Pbarr 8th Jan - 14:100 A4 480m, i then have a macro to copy a20 and paste it into a19 which i thought would run my macro but when i paste the 14:00 into cell a19 it changes the format from text to general and the macro wont fire?

Heres my macro, any help much appreciated i seem to be losing most of my dwindling hairline to this
thought of when marketid changes i use a

Code: Select all

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.ScreenUpdating = False
 If Target.Address = "$A$5" Then
        Dim raceTime As String
        raceTime = Target.Value
       
        If raceTime <> "" Then
           Worksheets("QUICK RACE VIEW").Activate
           For Counter = 1 To 30000
               Set curCell = Worksheets("QUICK RACE VIEW").Cells(Counter, 1)
               If curCell.Value <> "" Then
               
                   Application.DisplayAlerts = False
                   
                   If InStr(1, curCell.Value, raceTime) > 0 Then
   
                   Rows(CStr(curCell.Row) & ":" & CStr(curCell.Row + 6)).copy
                   Worksheets("MASTER").Range("A11:g18").PasteSpecial xlPasteValues, SkipBlanks:=True
                   Application.CutCopyMode = False
       
                   Application.DisplayAlerts = True
                   
                   End If
               End If
           Next Counter
           Worksheets("MASTER").Activate
         
    Range("a6").Select
    Application.ScreenUpdating = True
        End If