Error 13 type mismatch

[rjplante]

I have the change macro listed below that flags a run time error 13 type mismatch when a merged cell is changed. Any thoughts?

Thanks,

Robert

Dim PreviousValue

Private Sub Worksheet_Change(ByVal Target As Range)

'If signed indicator is P then abort.
If ThisWorkbook.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.
    ThisWorkbook.Sheets("Audit Log Conicals").Visible = True
    ThisWorkbook.Sheets("Audit Log Conicals").Activate

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

    'Adds the username to the second column in the first available row.
    ThisWorkbook.Sheets("Audit Log Conicals").Cells(65000, 2).End(xlUp).Offset(1, 0).Value = Sheets("Intro Page").Range("R15").Value
    ThisWorkbook.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.
    ThisWorkbook.ActiveSheet.Protect Password:="bioe1025"
    
    'Hides the Audit Log Bag worksheet.
    ThisWorkbook.Sheets("Audit Log Conicals").Visible = False

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

End Sub

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

 

[Juan Pablo González]

You get the error because the .Value property returns a variant array, with each element containing the value of each cell in the merged area.

Use

Target.Cells(1, 1).Value

instead.

 

[rjplante]

Thank you for the suggestion. I have tried making the change to:

ElseIf Target.Cells(1, 1).Value <> PreviousValue Then

but I still get the same error when I make a change that launches the macro. It also sill only affects the merged cells.

What else can I try?

Thanks a million for all the help.

Robert
(Beginner VBA programmer, trying to pass for intermediate level programmer)

 

[Richard Schollar]

Hi Robert

You also need to amend the way PreviousValue is assigned a value:

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

 

[Norie]

Robert

Do you really need the merged cells?

They could be causing the problem.

Where is the code located anyway?

Is it in the worksheet 'QC5003.4A-1 FINAL QC CONICAL'?


PS Why does the comment say if 'signed indicator is P then abort' when you actually check for 'C'?

 

[rjplante]

Yes the code is in the QC5003.4A-1 FINAL QC CONICAL worksheet code window. After I amended the way the previous value is assigned, the code works awesome.

Thanks a ton,

Robert

 

[Norie]

Robert

Glad to see you got it working.

I asked which worksheet it was in because you were explicity referencing 'QC5003...'.

You don't need to do that, and you don't need to activate or select or unhide the audit worksheet.

Anyway you've got it working and that's the important thing.