Type mismatch error - 'run-time error 13'

[Al]

I have some code running in a worksheet (sheet 1) to hide two worksheets
unless the value (text) in a cell (D4:E4 merged) of 'sheet 1' is a certain
value. The value in 'D4:E4' is chosen from a list of 4 options (set up using
a data validation>list).

I am getting a type mismatch error if the value in 'd4' is deleted/cleared
(not much of an issue as should only select form the list). More of an issue
is I get the same error when any other merged cells (i.e. "C2:I2", "B18:J20")
are cleared (i.e select cell and hit 'Delete').

Any ideas on why the error occurs or how to avoid it?

The code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D4") Then
If Target.Value = "SMART Cable" Then
Sheets("Load v Distance").Visible = True
Sheets("Load v Time").Visible = True
Else
Sheets("Load v Distance").Visible = False
Sheets("Load v Time").Visible = False
End If
End If
End Sub

 

[Jacob Skaria]

Try with the below.

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$D$4:$E$4" Then
If Range("D4") = "SMART Cable" Then
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Else
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
End If
End If

End Sub

 

[Tim Zych]

This should help:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("D4")) Is Nothing Then
If Target.Cells(1, 1).Value = "SMART Cable" Then
Sheets("Load v Distance").Visible = True
Sheets("Load v Time").Visible = True
Else
Sheets("Load v Distance").Visible = False
Sheets("Load v Time").Visible = False
End If
End If
End Sub

 

[Al]

Hi Jacob, thanks for the quick reply however that code does not hide the
sheets.

 

[Al]

Tim,

that works a treat, thanks very much. Can't quite understand how but it
works. Cheers.

 

[Jacob Skaria]

Change the sheet names from Sheet2,Sheet3 to to "Load v Distance" & "Load v
Time"

If this post helps click Yes

 

[Jacob Skaria]

Please try the below and feedback. Variable lngLastRow will have the last row

Sub test()
Dim lngLastRow As Long

With ActiveWorkbook.Sheets("Database")
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
..Range("A" & lngLastRow) = ActiveSheet.Range("E1")
..Range("B" & lngLastRow) = ActiveSheet.Range("B3")
..Range("C" & lngLastRow) = ActiveSheet.Range("B1")
..Range("G" & lngLastRow) = ActiveSheet.Range("E5")
..Range("J" & lngLastRow) = ActiveSheet.Range("B55")
..Range("K" & lngLastRow) = ActiveSheet.Range("E2")
End With

End Sub

 

[Tim Zych]

Glad to help.

To explain it a bit more:

The line above makes sure that the changed-cell intersects with D4 before
evaluating anything.

The other issue was when clearing D4, and since D4 was merged with E4, it
resulted in a change to 2 cells. Investigate it and you'll see the issue:

Here is the code with a Stop. Enter a value, then clear it and it will stop
at the stop point. Step through and debug.print the address.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("D4")) Is Nothing Then

If Target.Cells.Count > 1 Then Stop '< -- when clearing D4 it
will stop here.
Debug.Print Target.Address '< -- results in
$D$4:$E$4 because it is merged.

If Target.Cells(1, 1).Value = "SMART Cable" Then '< -- top left
cell, for single or multi cells.
Sheets("Load v Distance").Visible = True
Sheets("Load v Time").Visible = True
Else
Sheets("Load v Distance").Visible = False
Sheets("Load v Time").Visible = False
End If
End If
End Sub

"Target.Cells(1,1)" returns the top-left cell of a single or muti-cell
range. Normally, Target is only a single cell. But when it is part of a
merged area, it sometimes evaluates as more than one cell -- like when
clearing D4. Cells(1,1) returns the top left cell.

When using merged cells, workaround code such as this is needed. But the
code is safe enough to continue to work even if you decide to un-merge
D4:E4.

 

[Jacob Skaria]

Sorry the below is a wrong post...

 

[Jacob Skaria]

No Chris. You can add the below to the end..

Activeworkbook.Save

If this post helps click Yes

 

[Patrick Molloy]

change
If Target.Address = "$D$4:$E$4" Then

to

If Target.Address = "$D$4" Then

Even though the two cells are merged, only the "active" cell, D4 is passed
to the event handler

 

[keiji kounoike]

I don't think so. Tim has already explained the cause of error. I think
your change couldn't solve the problem.
Other than Tim's way, The code like

If Target.Cells(1, 1) = Range("D4") Then
If Target.Cells(1, 1) = "SMART Cable" Then

might work.

Keiji

 

[Patrick Molloy]

my test worked otherwise i wouldn't have posted it. If you step into the sub
and ?target.Address it returns $D$4

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$4" Then
Select Case Target.Value
Case "A"
Sheet2.Visible = xlSheetHidden
Sheet3.Visible = xlSheetHidden
Case "B"
Sheet2.Visible = xlSheetVisible
Sheet3.Visible = xlSheetVisible
End Select
End If
End Sub

 

[keiji kounoike]

How did you test?

test the code the below.
first merge E4 and D4. After that put any character into merged cell and
push enter key, then you could see msgbox saying $D$4, then again select
merged cell and push Del key, this time you could see msgbox saying
$D$4:$E$4.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$4" Then
MsgBox Target.Address
Else
MsgBox Target.Address
End If
End Sub

Keiji

 

[Tim Zych]

Patrick:

If you clear out D4, Target is $D$4:$E$4 due to the merged cell. Your macro
has no condition to handle that.