| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
showing hidden rows on a protected sheet
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing hidden rows on a protected sheet
can someone please please help with this
-- Cathy "cathy" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing hidden rows on a protected sheet
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 wrote: 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 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing hidden rows on a protected sheet
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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing hidden rows on a protected sheet
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 wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing hidden rows on a protected sheet
SUCCESS!! Many thanks!
-- Cathy "Dave Peterson" wrote: 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 wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing hidden rows on a protected sheet
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 "Dave Peterson" wrote: 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 wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing hidden rows on a protected sheet
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. cathy wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing hidden rows on a protected sheet
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 "Dave Peterson" wrote: 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. cathy wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing hidden rows on a protected sheet
I should say they have at least Excel 2003 or higher.
-- Cathy "Dave Peterson" wrote: 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. cathy wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing hidden rows on a protected sheet
In xl2003 menus, the security setting can be found in:
Tools|Macro|Security|Security level tab cathy wrote: 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 "Dave Peterson" wrote: 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. cathy wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy a page with hidden rows and only paste what is showing | Excel Discussion (Misc queries) | |||
| I need my Hidden Rows to stay hidden when I print the sheet. | Excel Discussion (Misc queries) | |||
| protected sheets and hidden rows | Excel Discussion (Misc queries) | |||
| deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions | |||
| change excel row height without showing hidden rows | Excel Worksheet Functions | |||
Linear Mode
