2 macros, 1 module don't work together

[alina_t]

Hi all,
I have two macros to do different tasks in one worksheet. One uses the Workbook_change event, the other uses a button.
The first macro runs when the user inputs a date into a cell in the column K. The macro checks the corresponding test type in the same row and finds if there is another test of the same type scheduled (test types are in the same column). If there is, it checks if the date is scheduled far enough ahead that the first test is finished before the second one can be started.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Val_Date(100)

    If Not Intersect(Target, Range("K1:K64000")) Is Nothing Then
        ' At least one cell of Target is within the range K1:K64000.
        ' Carry out some action.
        col_K = Target.Address
       ' MsgBox "You just changed " & Target.Address
        Range(col_K).Activate
        Date_Cur = ActiveCell.Value ' Date entered
        Duration_Cur = ActiveCell.Offset(0, -4).Value
        'Move to col_A
        ActiveCell.Offset(0, -6).Select
        Value = ActiveCell.Value
        'MsgBox "Value = " & Value ' value in col A
        'Search for matching values
        iRowL = Cells(Rows.Count, 5).End(xlUp).Row 'number of rows
        i = 0
        For iRow = 2 To iRowL - 1
        If Not IsEmpty(Cells(iRow, 5)) Then
            Value1 = Cells(iRow, 5).Value
            'MsgBox "Value = " & Value & "  Value1 = " & Value1
            If (Value = Value1) Then
                i = i + 1
                Val_Date(i) = Cells(iRow, 11)
                'MsgBox "index= " & i & " date= " & Val_Date(i)
            End If
        End If
        Next iRow
        For j = 1 To i
        'List all date values
       ' MsgBox "j = " & Val_Date(j)
        Next j
        
        '//Bubblesort dates
First = LBound(Val_Date)
Last = UBound(Val_Date)
For i = First To Last - 1
For j = i + 1 To Last
If Val_Date(i) > Val_Date(j) Then
Temp = Val_Date(j)
Val_Date(j) = Val_Date(i)
Val_Date(i) = Temp
End If
Next j
Next i

'//Max Date is last one in array
maXDate = Val_Date(UBound(Val_Date))

'MsgBox "max = " & maXDate

Delta = Date_Cur - maXDate ' difference between date entered and max date

'MsgBox "Delta = " & Delta & " Duration = " & Duration_Cur

If Delta < Duration_Cur Then
    MsgBox "The Test Can't be run on This Date", vbExclamation, "Warning"
    ActiveCell.Offset(0, 6).Select
End If
        
    Else
        ' No cell of Target in in the range K1:K64000. Get Out.
    End If

The second macro adds rows and copies formulas from the upper row into the new rows. There is a button in the cell "Number of tests" that calls the macro. If the first macro is not in the module, this button works fine.

Sub InsertRowsAndFillFormulas_caller()
  Call InsertRowsAndFillFormulas
End Sub

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' Documented:  http://www.mvps.org/dmcritchie/excel/insrtrow.htm
   Dim x As Long
   ActiveCell.EntireRow.Select  ' Selects entire row
      If vRows = 0 Then
    vRows = Application.InputBox(prompt:="How many rows do you want to add?", Title:="Add Rows", Default:=1, Type:=1)
       'Default for 1 row, type 1 is number
    If vRows = False Then Exit Sub
   End If

   Dim sht As Worksheet, shts() As String, b As Long
   ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
       Windows(1).SelectedSheets.Count)
   b = 0
   For Each sht In _
       Application.ActiveWorkbook.Windows(1).SelectedSheets
    Sheets(sht.Name).Select
    b = b + 1
    shts(b) = sht.Name

    x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup

    Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
     Resize(rowsize:=vRows).Insert Shift:=xlDown

    Selection.AutoFill Selection.Resize( _
     rowsize:=vRows + 1), xlFillDefault

    On Error Resume Next    
    ' to remove the non-formulas -- 1998/03/11 Bill Manville
    Selection.Offset(1).Resize(vRows).EntireRow. _
     SpecialCells(xlConstants).ClearContents
   Next sht
   Worksheets(shts).Select
End Sub

When both of them are there, this macro will add rows, but then I'd get Error 13 (type mismatch). Excel shows that the error is in the first macro. How do I solve this?

Any help would be appreciated.

 

[VoG]

You can disable the event code like this

Sub InsertRowsAndFillFormulas_caller()
Application.EnableEvents = False
  Call InsertRowsAndFillFormulas
Application.EnableEvents = True
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

At the beginning of your InsertRowsAndFillFormulas procedure put:

Application.EnableEvents = False

and at the end put:

Application.EnableEvents = True

 

[alina_t]

Thanks for the help. I've tried using Application.EnableEvent, and, well, I get a Run-time error 1004. The following line is selected.

        Duration_Cur = ActiveCell.Offset(0, -4).Value

Since I just added a row, there is nothing in that cell. Also, that column uses a VLOOKUP function when a user enters a value into column E. Of course, there is nothing there, either.

 

[Andrew Poulsom]

What is the text of the error? You should use Target rather than ActiveCell.

 

[alina_t]

Andrew, I solved the issue. I got the error because the new row had blank cells. For the cell in question, the test duration is returned. Since that cell uses the Excel VLOOKUP formula to fill in a number from a table, it returns an #N/A error in the cell when it has nothing to look up.
I got around the issue by filling in the constants in the new row.

Thanks for your help!