Stop Debugging !!

I have the following code in a sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" And Target.Value <> "" Then
For Each c In Range("C5:R5")
If c.Value <> Target.Value Then
c.EntireColumn.Hidden = True
End If
Next
ElseIf Target.Address = "$B$4" And Target.Value = "" Then
Columns("C:R").Select
Selection.EntireColumn.Hidden = False
Range("A4").Select
End If
End Sub

It hides all columns except the one referenced in a drop-down menu in Cell
B4. However, if I make changes anywhere else on the sheet it trys to run and
I get:
Run-time error '13':
Type mismatch

All help appreciated !

Phil

works fine as is.
I generall have Option Explicit as a defualt, which means I needed to add
DIM c as Range
otherwise I wasn't able to reproduce your issue.
If you put a break in th ecode, at what line does it raise the error?


"Phil Osman" wrote:

> I have the following code in a sheet:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$B$4" And Target.Value <> "" Then
> For Each c In Range("C5:R5")
> If c.Value <> Target.Value Then
> c.EntireColumn.Hidden = True
> End If
> Next
> ElseIf Target.Address = "$B$4" And Target.Value = "" Then
> Columns("C:R").Select
> Selection.EntireColumn.Hidden = False
> Range("A4").Select
> End If
> End Sub
>
> It hides all columns except the one referenced in a drop-down menu in Cell
> B4. However, if I make changes anywhere else on the sheet it trys to run and
> I get:
> Run-time error '13':
> Type mismatch
>
> All help appreciated !
>
> Phil

Hi,
where's the yellow line stop?

Also, try adding Option Explicit at the top of the module and
Dimensioning c as Range

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c as Range
.....


Hth,
OJ

Phil
I changed your logic a little and it seems ok. I also put an on error
resume next to ignore any errors you do get. I think your code was erroring
because you checked the value no matter which cell was the target - this
version only does the work if they changed B4. Your version would error
anytime there was an error value in the target cell, and probably other
random times too as you need to use typed variables. Note too the 'for each'
loop - its for each cell, I think yours may have been using the default
(value) which would also cause the type mismatch.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
On Error Resume Next
If Target.Address = "$B$4" Then
If Target.Value <> "" Then
For Each c In Range("C5:R5").Cells
If c.Value <> Target.Value Then
c.EntireColumn.Hidden = True
End If
Next c
ElseIf Target.Value = "" Then
Columns("C:R").EntireColumn.Hidden = False
'Range("A4").Select
End If
Else
'changed cell is not B4 - do nothing
End If
End Sub

cheers
Simon

"Phil Osman" wrote:

> I have the following code in a sheet:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$B$4" And Target.Value <> "" Then
> For Each c In Range("C5:R5")
> If c.Value <> Target.Value Then
> c.EntireColumn.Hidden = True
> End If
> Next
> ElseIf Target.Address = "$B$4" And Target.Value = "" Then
> Columns("C:R").Select
> Selection.EntireColumn.Hidden = False
> Range("A4").Select
> End If
> End Sub
>
> It hides all columns except the one referenced in a drop-down menu in Cell
> B4. However, if I make changes anywhere else on the sheet it trys to run and
> I get:
> Run-time error '13':
> Type mismatch
>
> All help appreciated !
>
> Phil

That seems to have done the job, thanks indeed !

Phil

"Simon Murphy" wrote:

> Phil
> I changed your logic a little and it seems ok. I also put an on error
> resume next to ignore any errors you do get. I think your code was erroring
> because you checked the value no matter which cell was the target - this
> version only does the work if they changed B4. Your version would error
> anytime there was an error value in the target cell, and probably other
> random times too as you need to use typed variables. Note too the 'for each'
> loop - its for each cell, I think yours may have been using the default
> (value) which would also cause the type mismatch.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim c As Range
> On Error Resume Next
> If Target.Address = "$B$4" Then
> If Target.Value <> "" Then
> For Each c In Range("C5:R5").Cells
> If c.Value <> Target.Value Then
> c.EntireColumn.Hidden = True
> End If
> Next c
> ElseIf Target.Value = "" Then
> Columns("C:R").EntireColumn.Hidden = False
> 'Range("A4").Select
> End If
> Else
> 'changed cell is not B4 - do nothing
> End If
> End Sub
>
> cheers
> Simon
>
> "Phil Osman" wrote:
>
> > I have the following code in a sheet:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Address = "$B$4" And Target.Value <> "" Then
> > For Each c In Range("C5:R5")
> > If c.Value <> Target.Value Then
> > c.EntireColumn.Hidden = True
> > End If
> > Next
> > ElseIf Target.Address = "$B$4" And Target.Value = "" Then
> > Columns("C:R").Select
> > Selection.EntireColumn.Hidden = False
> > Range("A4").Select
> > End If
> > End Sub
> >
> > It hides all columns except the one referenced in a drop-down menu in Cell
> > B4. However, if I make changes anywhere else on the sheet it trys to run and
> > I get:
> > Run-time error '13':
> > Type mismatch
> >
> > All help appreciated !
> >
> > Phil