Visual Basic code not working when sheet is saved
 

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

Visual Basic code not working when sheet is saved
 

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

Regards,
Stefi

€žjoe123€� ezt Ã*rta:

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

Visual Basic code not working when sheet is saved
 

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






"Stefi" wrote:

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

Regards,
Stefi

€žjoe123€� ezt Ã*rta:

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

Visual Basic code not working when sheet is saved
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"joe123" wrote in message
...
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