Visual Basic code not working when sheet is saved

[joe123]

hi i have managed to get my code to work but once i save the sheet as a
template or as just a normal save file, and open it back up it will not work
again. i also want to lock and protect the sheet but have not got this far
yet.

the code is

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
On Error Resume Next
If Target.Address = "$C$3" And Target.Value <> "" Then
Rows("39:47").EntireRow.Hidden = True
Select Case Target.Value
Case "Integra"
Rows("39:47").EntireRow.Hidden = False

End Select
End If
End Sub

and it works fine when i put it in first but after it seems to die on me

any help would be very greatfull and i have looked for a solution already
but am unable to find one.
i also am not very good at vb code and most was found through help pages
thank you

 

[Stefi]

Try in View>Immediate window:
?application.enableevents
press Enter
If it returns False then type
application.enableevents=True
and press Enter

Regards,
Stefi

„joe123†ezt írta:

 

[Joel]

Remove the ON Error statement because it may be hidding some real errors.
the code will stop when an wrror is reached.

I think you may want to put some of this code also in a workbook open
function.

Als I would change the following statment so if multiple cells are changed
at the same time (like copying a row) the code will still work

from
If Target.Address = "$C$3" And Target.Value <> "" Then

to

if not intersect(Target,Range("C3")) is nothing and _
Range("C3") <> "" then

 

[Don Guillett]

I could not replicate your problem. Could it be that you did not type
Integra properly?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
'On Error Resume Next 'probably not needed
If Target.Address = "$C$3" And Target.Value <> "" Then
Rows("39:47").Hidden = True
Select Case ucase(application.trim(Target.Value))
Case "INTEGRA"
Rows("39:47").Hidden = False
End Select
End If
End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
'On Error Resume Next 'probably not needed
If Target.Address = "$C$3" And Target.Value <> "" Then

Select Case ucase(application.trim(Target.Value))
Case "INTEGRA"
Rows("39:47").Hidden = False
case else
Rows("39:47").Hidden = True
End Select
End If
End Sub