run time error 9

[rjplante]

I don't know if I can describe the process of what is happening accurately enough, but I'll give it a try.

In workbook A, I have code in the worksheets code window to monitor a target.address change in cell U46 (macro 1). When this happens it turns on an audit trail macro that is supposed to record any changes to the worksheet. It starts by trying to record the change in cell U46.

The change in cell U46 is initiated by another macro {macro 2}, which does the following:
1) Change U46 to "C"
2) display message box to ask user to transfer data to workbook B.
3) If YES, {Call transfer macro [macro 3]} open workbook B, transfer data, save and close workbook B.
4) display message box that data transfer was successful.

The problem is that when I change cell U46 the change macro is attempting to run. Line #2 runs fine (after a long delay {15 seconds}). the data is transferred, but workbook B does not save and close.

I then get a "run-time error '9' - subscript is out of range" message with the line highlighted in the original audit trail code (see below red text for break line).

My question is I was wonder if there is a way to suspend the change event macro (macro 1) while the other macro (macro 2 and macro 3) finish running, or suspend macro 2 and 3 while macro 1 finishes?

Any direction is appreciated.

Thanks,
Robert

Dim PreviousValue
'-------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

'If signed indicator is P then abort.
If Sheets("QC5003.4A-1 FINAL QC CONICAL").Range("U46").Value <> "C" Then
    Exit Sub

ElseIf Target.Value <> PreviousValue Then

    'Suspends display updates as the macro executes.
    Application.ScreenUpdating = False

    'Makes the Audit Log Bag worksheet visible and active.
    Sheets("Audit Log Conicals").Visible = True
    Sheets("Audit Log Conicals").Activate

    'Enters the password to unlock the worksheet and allow data entry.
    ActiveSheet.Unprotect Password:="bioe1025"

    'Adds the username to the second column in the first available row.
    Sheets("Audit Log Conicals").Cells(65000, 2).End(xlUp).Offset(1, 0).Value = Sheets("Intro Page").Range("R15").Value
    Sheets("Audit Log Conicals").Cells(65000, 2).End(xlUp).Select
    ActiveCell.Offset(0, 1).Value = Date  'Enters the date in the next cell
    ActiveCell.Offset(0, 2).Value = Time  'Enters the time in the next cell.
    ActiveCell.Offset(0, 3).Value = Sheets("QC5003.4A-1 FINAL QC CONICAL").Name  'Enters the sheet name in the next cell.
    ActiveCell.Offset(0, 4).Value = Target.Address  'Enters the cell name in the next cell.
    ActiveCell.Offset(0, 5).Value = PreviousValue  'Enters the previous value of the cell.
    ActiveCell.Offset(0, 6).Value = Target.Value  'Enters the new value of the cell.

    'Enters the password to protect the workbook.
    ActiveSheet.Protect Password:="bioe1025"
    
    'Hides the Audit Log Bag worksheet.
    Sheets("Audit Log Bag").Visible = False

    'Makes the QC5003.4A-1 FINAL QC CONICAL the active sheet.
    Sheets("QC5003.4A-1 FINAL QC CONICAL").Activate
    
    ActiveWorkbook.Save  'Saves the active workbook.
    
'Restores display updates
Application.ScreenUpdating = True
End If

End Sub

'--------------------------------------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value
End Sub

 

[Bieli]

You can use some flags in macros that will allow executing rest of it.
I don't know can you change variable flag from one workbook in other but you can put this flag in some cell then change it/chack it.
or
In workbook2 you can make simply macro that will change global flag and this macro can be lunch from workbook1 and chacked in workbook2.

 

[Norie]

You can suspend all event code using this:

 Application.EnableEvents=False

You might not need to though.

Which workbook is the worksheet 'QC5003.4A-1 FINAL QC CONICAL' in?

Is it the same workbook as this worksheet event code is in?

 

[rjplante]

Yes it is. The only workbook the is external is PCB Conicals Trendline or Workbook B.

 

[Norie]

Have you checked the name?

Perhaps even just an errant space somewhere.

 

[Norie]

Actually scratch that.

Try adding ThisWorkbook in front of it.

That should make sure that the correct workbook is referenced.

 

[rjplante]

Adding thisworkbook fixed the problem with the code error.
Thanks