showing hidden rows on a protected sheet

[cathy]

I have a password protect worksheet. Cell E30 is a data validation drop down
list that offers two options "0" or "1". When "1" is selected unhidden rows
144 - 370 appear. When "0" is selected, they remain hidden.

This works great until I set up the protection. Then the hidden cells do
not appear as they are suppose to.

How can I get around this?
I'm not that well versed in code - so any detail is appreciated.

 

[cathy]

can someone please please help with this

 

[Dave Peterson]

I bet you're using some event code (worksheet_change event???) to hide/show
those rows.

You can add a line to unprotect the worksheet, do the work, then reprotect the
worksheet:

me.unprotect password:="Cathy!"
'do the work that hides/shows
me.protect password:="Cathy!"

 

[cathy]

Dave:

I added what you said into the code below & I received an error message.
Here's how it looks.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$30" Then
Application.ScreenUpdating = False
If Range("E30").Value = "0" Then
Rows("144:370").Hidden = True
ElseIf Range("E30").Value = "1" Then
Rows("144:370").Hidden = False
Me.Unprotect Password:="Cathy!"
'do the work that hides/shows
Me.Protect Password:="Cathy!"
End If
Application.ScreenUpdating = True
End If
End Sub

 

[Dave Peterson]

First, remember to use the real password--I guessed at "Cathy!".

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$30" Then
Application.ScreenUpdating = False
me.unprotect password:="your real password here!"
If me.Range("E30").Value = 0 Then
me.Rows("144:370").Hidden = True
ElseIf Range("E30").Value = 1 Then
me.Rows("144:370").Hidden = False
End If
me.protect password:="your real password here!"
Application.ScreenUpdating = True
End If
End Sub

Dave:

I added what you said into the code below & I received an error message.
Here's how it looks.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$30" Then
Application.ScreenUpdating = False
If Range("E30").Value = "0" Then
Rows("144:370").Hidden = True
ElseIf Range("E30").Value = "1" Then
Rows("144:370").Hidden = False
Me.Unprotect Password:="Cathy!"
'do the work that hides/shows
Me.Protect Password:="Cathy!"
End If
Application.ScreenUpdating = True
End If
End Sub

 

[cathy]

SUCCESS!! Many thanks!
--
Cathy

First, remember to use the real password--I guessed at "Cathy!".

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$30" Then
Application.ScreenUpdating = False
me.unprotect password:="your real password here!"
If me.Range("E30").Value = 0 Then
me.Rows("144:370").Hidden = True
ElseIf Range("E30").Value = 1 Then
me.Rows("144:370").Hidden = False
End If
me.protect password:="your real password here!"
Application.ScreenUpdating = True
End If
End Sub

 

[cathy]

Dave:

When I emailed this out to other "users" - it doesn't work for them.
However, it works fine for me. I should note that this workbook has some
unlocked cells for the sales team to select from the drop downs.

How can I get this to work for the team?

 

[Dave Peterson]

Did you email your workbook that worked fine or did you email the macro and tell
them to install it?

If you emailed the workbook, make sure that those users allow macros to run when
they open the workbook. And if the other users are using xl97, you may have to
make another change to the data|validation cell.

If you emailed the macro, then I'm guessing that they didn't do something
correctly when they installed it.

Dave:

When I emailed this out to other "users" - it doesn't work for them.
However, it works fine for me. I should note that this workbook has some
unlocked cells for the sales team to select from the drop downs.

How can I get this to work for the team?

 

[cathy]

I emailed the workbook (that worked fine for me). Everyone should be using
excel 2003. I have no idea how to email a macro. My boss figured it might
be a security setting but neither of us know where to go in order to "fix"
the problem.

 

[cathy]

I should say they have at least Excel 2003 or higher.

 

[Dave Peterson]

In xl2003 menus, the security setting can be found in:
Tools|Macro|Security|Security level tab

I emailed the workbook (that worked fine for me). Everyone should be using
excel 2003. I have no idea how to email a macro. My boss figured it might
be a security setting but neither of us know where to go in order to "fix"
the problem.
--
Cathy

Did you email your workbook that worked fine or did you email the macro and tell
them to install it?

If you emailed the workbook, make sure that those users allow macros to run when
they open the workbook. And if the other users are using xl97, you may have to
make another change to the data|validation cell.

If you emailed the macro, then I'm guessing that they didn't do something
correctly when they installed it.