Excel 2000 macros not fully working on Excel 2007

[voregel]

Hi,
I have a worksheet that was created in Excel 2000 which has several macros and works well in this environment. However, they have upgraded to Excel 2007 and the worksheet no longer runs correctly without any intervention. If I manually step-thru the macros, the worksheet runs OK. But I have to manually step over a SUB to jump to the next SUB and complete the process.
Not sure what is keeping the macros from continuing to run through all the macros as programmed.

Here is part of the program that I am having issues with....

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"
Worksheets("scanupdate").OnData = "fivesecondscandelay"
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

Sub getscandata()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
current_sheet = ActiveSheet.Name
Sheets("Input").Select
current_roll = Range("B2").Value
scan_count = Range("B4").Value
If current_roll <> previous_roll Then
Call loadeorprogress
Application.StatusBar = "Getting Scan Data " & TimeValue(Now)
previous_roll = current_roll
Else

....The only macro that runs is the first one (start_collection) and never continues on to "getscandata", as it was programmed to.
Also, I would like to automatically run the "start_collection" macro upon opening the worksheet and I have tried implementing the following and need to confirm it is correct:

Sub Autpen()
Application.DisplayAlerts = False
start_collection
ThisWorkbook.Saved = True
End Sub

Thanks for the help!!

 

[madonnac]

I don't see a line where you call [tt]GetScanData[/tt] from the first macro, [tt]Start_Collection[/tt].

The autorun macro should be in [tt]Workbook_Open[/tt], with the code in the [tt]ThisWorkBook[/tt] object

Private Sub Workbook_Open()
Application.DisplayAlerts = False
start_collection
GetScanData
ThisWorkbook.Saved = True
End Sub

 

[voregel]

Thanks for the reply. The GetScanData is called by fivesecondscandelay,

Sub fivesecondscandelay()
Application.OnTime Now + TimeValue("00:00:05"), "getscandata"
End Sub

I think I am close to my problem, what happend during the Excel upgrade, we also lost the DDE links and updated to OPC links.
Thus, for some reason the
Worksheets("scanupdate").OnData = "fivesecondscandelay" is not running correctly. My data is changing, but not doing anything in the macro.
perhaps the .OnData needs to be updated to an events?

Basically what I need is for the fivesecondscandelay to run when a cell in scanupdate changes value.

Thanks

 

[voregel]

bump

 

[voregel]

Anyone??

 

[SkipVought]

what's your code for getscandata?

Skip,

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

 

[voregel]

Its sort of complex, but here it is anyway.
If I just run this Sub by steping through it, it runs just fine, its getting here from the previous code that is my problem.

Sub getscandata()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    current_sheet = ActiveSheet.Name
    Sheets("Input").Select
    current_roll = Range("B2").Value
    scan_count = Range("B4").Value
    If current_roll <> previous_roll Then
        Call loadeorprogress
        Application.StatusBar = "Getting Scan Data " & TimeValue(Now)
        previous_roll = current_roll
    Else
        Sheets("Input").Select
        If scan_count = 0 Then
            current_length = Worksheets("Input").Range("D2").Value
            scan_average = Range("N2").Value
            Target = Worksheets("Input").Range("W2").Value
            minscale = Range("B8").Value
            maxscale = Range("B7").Value
            rollid = Sheets("Input").Range("B2").Value
            lot_number = Sheets("Input").Range("I2").Value
            recipe_name = Sheets("Input").Range("K2").Value
            Sheets("Roll Data").Select
            Application.StatusBar = "Getting Scan Data " & TimeValue(Now)
            Sheets("Input").Select
            Range("D2:AG2").Select
            Selection.Copy
            Sheets("Roll Data").Select
            Range(Cells(scan_count + 2, 1), Cells(scan_count + 2, 1)).Select
            Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:= _
                False, Transpose:=False
            numberoflanes = Application.Count(Range("U2:W2"))
            Range(Cells(2, 21), Cells(2, numberoflanes + 20)).Select
            ActiveWorkbook.Names.Add Name:="input_range", RefersTo:=Selection()
            previous_length = current_length
            Range("CR2:DF2").Copy
            Range("CR4:DF4").PasteSpecial Paste:=xlPasteValues, operation:=xlPasteSpecialOperationAdd, _
                skipblanks:=False, Transpose:=False
            Range("C1").Select
            lcladjuster = Worksheets("Input").Range("B9").Value
            ucladjuster = Worksheets("Input").Range("B10").Value
            previous_length = current_length
            Sheets("Roll Spec Chart").Select
            ActiveChart.ChartTitle.Select
            Selection.Characters.Text = "Roll Number " & rollid & Chr(13) _
                 & "Batch Number " & lot_number & Chr(13) & recipe_name
            ActiveChart.Axes(xlValue).Select
            With ActiveChart.Axes(xlValue)
                .MinimumScale = Target * 0.7
                .MaximumScale = Target * 1.3
                .MinorUnitIsAuto = True
                .MajorUnitIsAuto = True
                .Crosses = xlAutomatic
                .ReversePlotOrder = False
                .ScaleType = xlLinear
            End With
            Sheets("Roll Histogram").Select
            'ActiveSheet.Unprotect ("measurex")
            ActiveSheet.ChartObjects("Chart 1").Activate
            With Worksheets("Roll Histogram").ChartObjects("Chart 1")
                Set pa = ActiveChart.PlotArea
                'Set mychart = Charts("Chart 1")
                chartwidth = pa.InsideWidth
                chartheight = pa.InsideHeight
                chartleft = pa.InsideLeft
                charttop = pa.InsideTop
                cwidth = chartwidth / 21
                With ActiveChart.Shapes("Line 1")
                    .Height = chartheight
                    .Top = charttop
                    .Left = (chartwidth / 2) - (lcladjuster * cwidth) - cwidth * 2.5
                End With
                With ActiveChart.Shapes("Line 2")
                    .Height = chartheight
                    .Top = charttop
                    .Left = (chartwidth / 2) + (ucladjuster * cwidth) + cwidth * 5.5
                End With
                With ActiveChart.Shapes("Line 3")
                    .Height = chartheight
                    .Top = charttop
                    .Left = chartwidth / 2 + cwidth * 2
                End With
                With ActiveChart.Shapes("Text Box 4")
                    .Top = charttop
                    .Left = (chartwidth / 2) - (lcladjuster * cwidth) - cwidth * 2.5
                End With
                With ActiveChart.Shapes("Text Box 5")
                    .Top = charttop
                    .Left = (chartwidth / 2) + (ucladjuster * cwidth) + cwidth * 5.5
                End With
                With ActiveChart.Shapes("Text Box 6")
                    .Top = charttop
                    .Left = chartwidth / 2 + cwidth * 2
                End With
            End With
            Range("A1").Select
            Sheets("Input").Select
        Else
            current_length = Worksheets("Input").Range("D2").Value
            If current_length <> previous_length Then
                numberoflanes = Application.Count(Range("U2:W2"))
                Application.StatusBar = "Getting Scan Data " & TimeValue(Now)
                Sheets("Input").Select
                Range("D2:AG2").Select
                Selection.Copy
                Sheets("Roll Data").Select
                Range(Cells(scan_count + 2, 1), Cells(scan_count + 2, 1)).Select
                Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:= _
                    False, Transpose:=False
                Range(Cells(scan_count + 2, 21), Cells(scan_count + 2, numberoflanes + 20)).Select
                ActiveWorkbook.Names.Add Name:="input_range", RefersTo:=Selection()
                Range("CR2:DF2").Copy
                Range("CR4:DF4").PasteSpecial Paste:=xlPasteValues, operation:=xlPasteSpecialOperationAdd, _
                    skipblanks:=False, Transpose:=False
                previous_length = current_length
                roll_spec_data = "R" & Format(scan_count + 2, "0") & ":W" & Format(scan_count + 2, "0")
                Sheets("Roll Spec Chart").Select
                ActiveChart.SeriesCollection.Extend Source:=Sheets("Roll Data").Range( _
                    roll_spec_data), Rowcol:=xlColumns, CategoryLabels:=False
            End If
        End If
    End If
    Sheets(current_sheet).Select
End Sub

 

[SkipVought]

So have you put some BREAKS in your PROCESS to observe what is happening when A1 changes?

What have you observed?

Skip,

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

 

[voregel]

Not sure I know how do manage that....? Im not at much of an expert when it comes VBA code. Any tips would great.

 

[PHV]

Have a look here:
faq705-7148

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

 

[voregel]

So I tried running breaks, but never really found anythiing (no error messages). Any other suggestions out there?

Thanks!

 

[zelgar]

Try changing fivesecondsdelay to the following (changes in bold & strikeout)

Sub fivesecondscandelay()
Application.OnTime Now + TimeValue("00:00:05"), "getscandata"
getscandata
End Sub