Allowing worksheet to be updated and keep protection in place

[The hammer 1956]

I have attempted to allow specific rows to be hidden and require the macro to protect/unprotect in the process. The macro works without a hitch until I add the protect/unprotect requirement. I'm a novist exploring macros to expand Excel. Here is what I am using:

Private Sub macrounprotect1()

Sheet1.Unprotect Password:="Money1"

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If (Range("c11").Value = "Real Estate Secured") Then
Rows("13:27").EntireRow.Hidden = False
Rows("28:47").EntireRow.Hidden = True
End If
If (Range("c11").Value = "Unsecured") Then
Rows("28:38").EntireRow.Hidden = False
Rows("13:27").EntireRow.Hidden = True
Rows("39:47").EntireRow.Hidden = True
End If
If (Range("c11").Value = "Secured Other") Then
Rows("39:47").EntireRow.Hidden = False
Rows("28:38").EntireRow.Hidden = True
Rows("13:27").EntireRow.Hidden = True
End If


End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Private Sub macroprotect2()

Sheet1.Protect Password:="Money1"

End Sub

[mikeTRON]

I am not sure exactly what you are trying to accomplish. What is not working correctly? What are you trying to do? What portion of the problem is encountering errors?

[The hammer 1956]

Errors are showing with the hidden rows. The rows shown in the worksheet are visible only when the cell containing "Real Estate Secured" is shown, as an example. All other rows are hidden; "Unsecured" and "Secured Other." When I placed protect/unprotect in the mix, the macro no longer works. The macro has to allow the sheet to become unprotected to hid and unhid the rows. The the protection needs to be placed to protect cell contents. Hopefully that is a better explanation....

[stnkynts]

You're using the worksheet_change event incorrectly. Notice the use of the "Target" range which is the cell that is being changed. I am of course assuming you want this code to be run when you enter data into cell C11.

Please Login or Register  to view this content.

[The hammer 1956]

This is a much cleaner solution to what I was trying to achieve. Thanks....it works great.