I'm confused as to why you are unprotecting the active sheet, but then
re-protecting all the sheets in the workbook at the end of the routine.
Wouldn't you only need to re-protect the single sheet you unprotected at the
beginning of the routine? In the code below, I used a slightly different
Target validation routine than Dave did, and only re-protected the one sheet
you unprotected rather than all the sheets in the workbook, plus I implement
a mathematical equivalent to your entire Select Case block to produce this
routine (which should do what I think your original code was intended to
do)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet
If Target.Cells.Count = 1 And Target.Address = "$D$3" Then
Me.Unprotect Password:="Secret"
Me.Columns("E:N").Hidden = False
Me.Columns(Chr(79 - Target.Value) & ":N").Hidden = True
Me.Protect Password:="Secret", UserInterFaceOnly:=True
End If
End Sub

--
Rick (MVP - Excel)


"Meg Partridge" wrote in message
...
It seems to have worked! Thank you so much!!!

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wSheet As Worksheet

If Target.Cells.Count 1 Then
Exit Sub
End If

If Intersect(Target, Me.Range("D3")) Is Nothing Then
Exit Sub
End If

Me.Unprotect Password:="Secret"

Me.Columns("E:N").Hidden = False

Select Case Target
Case Is = 1: Me.Columns("N").Hidden = True
Case Is = 2: Me.Columns("M:N").Hidden = True
Case Is = 3: Me.Columns("L:N").Hidden = True
Case Is = 4: Me.Columns("K:N").Hidden = True
Case Is = 5: Me.Columns("J:N").Hidden = True
Case Is = 6: Me.Columns("I:N").Hidden = True
Case Is = 7: Me.Columns("H:N").Hidden = True
Case Is = 8: Me.Columns("G:N").Hidden = True
Case Is = 9: Me.Columns("F:N").Hidden = True
Case Is = 10: Me.Columns("E:N").Hidden = True
End Select

For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", _
UserInterFaceOnly:=True
Next wSheet

End Sub


Meg Partridge wrote:

Hello - I am receiving a Run-time error '13' Type mismatch for the code
that
I have. After reading the help section of microsoft, I am still lost!
The
code I am using is listed below. Any help would be greatly
appreciated!!!!
Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D3") Then
Columns("E:N").Hidden = False
Select Case Target
Case Is = 1
Columns("N").Hidden = True
Case Is = 2
Columns("M:N").Hidden = True
Case Is = 3
Columns("L:N").Hidden = True
Case Is = 4
Columns("K:N").Hidden = True
Case Is = 5
Columns("J:N").Hidden = True
Case Is = 6
Columns("I:N").Hidden = True
Case Is = 7
Columns("H:N").Hidden = True
Case Is = 8
Columns("G:N").Hidden = True
Case Is = 9
Columns("F:N").Hidden = True
Case Is = 10
Columns("E:N").Hidden = True
For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", _
UserInterFaceOnly:=True
Next wSheet
End Select
End If
End Sub

--

Dave Peterson