Excel 2010 Worksheet_change(byval Target As Range) Function

[voregel]

Hi,
I am looking to see how to use the following Change function under Excel 2010 to run a macro within the worksheet.
I was previously using the Worksheets("scanupdate").OnData = "fivesecondscandelay" code to run my macro FiveSecondScanDelay (under Excel 2000/3), but for some reason not working in Excel 2010. I have ONE cell (A1) that has a value that updates about every 20-30 seconds, and then I want to use this "change" to run the FiveSecondScanDelay macro.

So I was looking into the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

How would I incorporate my macros to check whether cell A1 has changed on "scanupdate" worksheet?

Thanks!

 

[SkipVought]

hi,

I have ONE cell (A1) that has a value that updates

Private Sub Worksheet_Change(ByVal Target As Range)
  if not intersect(target, target.parent.cells(1,1)) is nothing then
     FiveSecondScanDelay 
  end if
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[voregel]

Thanks! I will give it a try and let you know.

 

[voregel]

Ok, ran into some issues, partially works. It appears to run once, then do nothing. I have cell A1 linked to an OPC server which, which updates about every 15 seconds, which is worksheet scanupdate, but after I inserted the above code into the Excel Object Sheet3 (scanupdate), it will only update once, when I know the value was updating constantly before the code.
Below is a partial code of the main Module1 where all macros reside.
The functionality of the line item I wanted to replace was Worksheets("scanupdate").OnData = "fivesecondscandelay" (see code below). But the Worksheet().OnData = " " does not work anymore, so I was looking for something to replace, hence the Worhseet_Change event.

Sub start_collection()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    'Run ("menu_control")
    Application.WindowState = xlMinimized
    Open "c:\excel\savestatus.txt" For Input As #1
    Line Input #1, spreadsave
    Line Input #1, reportprint
    Line Input #1, rollspecprint
    Close #1
    Application.StatusBar = "Start Data Collection"
    [b]Worksheets("scanupdate").OnData = "fivesecondscandelay"[/b]    
    Sheets("Input").Select
    previous_roll = Range("B2").Value
    current_roll = previous_roll
    previous_length = Range("D2").Value
    current_length = previous_length
    Sheets("Roll Data").Select
End Sub

Thanks in advance. Maybe I'm using the wrong event for my situation??

 

[SkipVought]

Put a break in your code and observe what code is actually executing when.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

What is OnData? I see no such property or method for the Worksheet object.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[voregel]

.OnData was used when it ran under Excel 2000 (looking for changes at cell A1) at the worksheet "scanupdate".

 

[voregel]

Maybe, I should ask what I need to happen, and perhaps that will be easier to understand my problem.

At this section of the code:

Application.StatusBar = "Start Data Collection"
[b]Worksheets("scanupdate").OnData = "fivesecondscandelay"[/b]

the macro is suppose to look at worksheet scanupdate for any changes to cell A1 (numeric value updated every 15 sec via an OPC Link), then it runs macro fivesecondscandelay, if it detected change in the value at A1.

So what other function or routine can I use in place of Worksheets("scanupdate").OnData = "fivesecondscandelay" to accomplish the same task??

That may help with the confusion.
Thanks!

 

[voregel]

Soo.....no good codes out there? I need a simple code that will check a worksheet in the workbook for any data value change and then run macro after data change/update (from external link OPC server).

Thanks!

 

[PHV]

What is OnData?

This is an XL95 property, deprecated since XL97 ...

http://www.bettersolutions.com/excel/ECN133/YN212910339.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[voregel]

I tried using the Worksheet.Change, but it would only update the data once and never run again.

Here is what I used for the code in the worksheet (scanupdate):

Private Sub Worksheet_Change(ByVal Target As Range)
                If Not Intersect(Target, Me.Range("A1")) Is Nothing Then fivesecondscandelay
End Sub

So what it was suppose to do is check for data value change on worksheet "scanupdate", then run the macro "fivesecondscandelay", which in return would call macro "get_scandata" to actually collect the data and move it to another worksheet at intervals of the data value changing from worksheet "scanupdate".

Does that make sense?

Thanks!