Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run macro on Cell Content Change (2000)

    I'm trying to get this macro to refresh an MS Query on Sheet2 (called "BOL DATA") using the contents of of cell K1 from Sheet1 as a filter. It's supposed to run everytime the value of K1 changes, but it doesn't seem to run at all. This macro is located on Sheet2 ("BOL DATA"). I tried to put the macro on Sheet1 but it wouldn't let me put the results of a qury on a different sheet. Am I using the right function?

    Private Sub worksheet_change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Worksheets("Sheet1").Range("K1")) Is Nothing Then
    Worksheets("BOL DATA").Range("A2").Select
    With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
    "ODBC;DSN=Max Dat;DATAPATH=m:kewillmaxdat;DDFPATH=m:kewillmaxdat ;NullEnabled=no;FeaturesUsed=no;AccessFriendly=yes ;DateFormat=m" _
    ), Array("dy;")), Destination:=Sheets("BOL TRACKING").Range("A2"))
    .CommandText = Array("SELECT ""BOL^Tracking"".TRACKNO, ""BOL^Tracking"".ADDR1, ""BOL^Tracking"".ADDR2, ""BOL^Tracking"".ADDR3, ""BOL^Tracking"".ADDR4, ""BOL^Tracking"".CHECKBY, ""BOL^Tracking"".CITY, ""BOL^Tracking"".CNTRY, ""BOL^Tracking""", _
    ".CUSTID, ""BOL^Tracking"".FRTAMT, ""BOL^Tracking"".FRTPAY, ""BOL^Tracking"".GROSSWEIGHT, ""BOL^Tracking"".GROSSWUOM, ""BOL^Tracking"".LOADBY, ""BOL^Tracking"".NAME, ""BOL^Tracking"".NETWEIGHT, ""BOL^Tracking"".NETWUOM, ""BOL^Tracking"".PREPBY, ""BOL^Tracking"".SPECINSTR1, ""BOL^Tracking"".SPECINSTR2, ""BOL^Tracking"".SPECINSTR3, ""BOL^Tracking"".SPECINSTR4, ""BOL^Tracking"".SPECINSTR5, ""BOL^Tracking"".STATE, ""BOL^Tracking", _
    ".STATUS, ""BOL^Tracking"".ZIPCD" & Chr(13) & "" & Chr(10) & "FROM ""BOL^Tracking"" ""BOL^Tracking""" & Chr(13) & "" & Chr(10) & "WHERE (""BOL^Tracking"".TRACKNO='" & Workbook.Worksheets("Shee1").Range("K1") & "')")
    .Refresh BackgroundQuery:=False
    End With
    End If
    End Sub

    Any advise would be appreciated.
    Stats

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run macro on Cell Content Change (2000)

    There are several possibilities:

    1- The code is not in the Worksheet module behind worksheet "BOL DATA". The default procedure statement for the worksheet change event looks like this:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    </pre>


    Since yours is different, you may not have the code in the correct place.

    2- The first If statement in the routine checks to see if the cell that was changed was on Worksheets("Sheet1"). Since you said this code was for Sheet2 which is named "BOL DATA", that If is always going to get a False, or an error depending whether or not there is a sheet named "Sheet1". In either case, the rest of the code in the procedure will never execute.

    I created a workbook with a sheet named "BOL DATA" and put the code below in its worksheet change event routine, and it is executed with the query code commented out.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Worksheets("BOL DATA").Range("K1")) Is Nothing Then
    Worksheets("BOL DATA").Range("A2").Select
    With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
    "ODBC;DSN=Max Dat;DATAPATH=m:kewillmaxdat;DDFPATH=m:kewillmaxdat ;NullEnabled=no;FeaturesUsed=no;AccessFriendly=yes ;DateFormat=m" _
    ), Array("dy;")), Destination:=Sheets("BOL TRACKING").Range("A2"))
    .CommandText = Array("SELECT ""BOL^Tracking"".TRACKNO, ""BOL^Tracking"".ADDR1, ""BOL^Tracking"".ADDR2, ""BOL^Tracking"".ADDR3, ""BOL^Tracking"".ADDR4, ""BOL^Tracking"".CHECKBY, ""BOL^Tracking"".CITY, ""BOL^Tracking"".CNTRY, ""BOL^Tracking""", _
    ".CUSTID, ""BOL^Tracking"".FRTAMT, ""BOL^Tracking"".FRTPAY, ""BOL^Tracking"".GROSSWEIGHT, ""BOL^Tracking"".GROSSWUOM, ""BOL^Tracking"".LOADBY, ""BOL^Tracking"".NAME, ""BOL^Tracking"".NETWEIGHT, ""BOL^Tracking"".NETWUOM, ""BOL^Tracking"".PREPBY, ""BOL^Tracking"".SPECINSTR1, ""BOL^Tracking"".SPECINSTR2, ""BOL^Tracking"".SPECINSTR3, ""BOL^Tracking"".SPECINSTR4, ""BOL^Tracking"".SPECINSTR5, ""BOL^Tracking"".STATE, ""BOL^Tracking", _
    ".STATUS, ""BOL^Tracking"".ZIPCD" & Chr(13) & "" & Chr(10) & "FROM ""BOL^Tracking"" ""BOL^Tracking""" & Chr(13) & "" & Chr(10) & "WHERE (""BOL^Tracking"".TRACKNO='" & Workbook.Worksheets("Shee1").Range("K1") & "')")
    .Refresh BackgroundQuery:=False
    End With
    End If
    End Sub
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run macro on Cell Content Change (2000)

    Good Morning Legare,
    Thanks for your help. I am however still having some problems.
    First, I put your example behind the sheet called BOL DATA and nothing happens when I change cell K1 on Sheet1.
    If I type in the Bill of Lading number into K1 on "BOL DATA" I get the following error; "Run-time error '424'...Object Required". When I press the debug button the three lines before ".Refresh BackgroundQuery:=False" are highlited in yellow.
    I can rearrange the workbook so the query is on the same page as the cell where the user enters the BOL # but I don't know about the error message. The coding that I gave in my origianl email came from the "Record a Macro" feature.
    Any help you can offer would be appreciated.
    Stats

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run macro on Cell Content Change (2000)

    That code must go in the worksheet module behind the sheet where the cell is being changed. If you are changing K1 on Sheet1, then the code must go behind Sheet1. If you are changing K1 on sheet "BOL DATA", then it must go behind that sheet. Altermnatively, you could put it in the Sheet Change event routine in the module behide the ThisWorkbook object which gets triggered when any cell on any sheet is changed.

    I can't help with the error on the Query. I don't know query and don't have any way to test it here. Maybe someone else will jump in with some help.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •