[vba] cannot hide/unhide rows when sheet is protected

[jpbonono]

Hi,

I have this code in my module

Private Sub Boys_Click()If Boys.Value = True Then
Rows("13:57").EntireRow.Hidden = True
Else
 Rows("13:57").EntireRow.Hidden = False
  End If
End Sub

However, my problem is whenever i protect the sheet it doesnt allow me to proceed(debug mode) since i am fully aware that some cells on the rows are protected to.

(I already tried modified the default cell locking(allowing rows) and still wont work)

Are there any work arounds here that would let excel allow the hiding/unhiding feature?

Thank you in advance.

 

[jpbonono]

I have a sneaky suspicion we are over complicating this. If the macro purely needs to hide or unhide the specified rows on button press, with no conditionals, just on button press try this:

Sub Boys_Click()
Application.ScreenUpdating = False
ActiveSheet.Unprotect ("password")
Rows("13:57").Hidden = Not Rows("13:57").Hidden
ActiveSheet.Protect ("password")
End Sub

This one also works, im still figuring if the formulas are safe.

 

[jpbonono]

What's wrong with this code?

[COLOR=#373E4D][FONT=helvetica]Sub HideRow()Dim lLastRow As LongDim lCounter As Long    Application.ScreenUpdating = False    Application.Calculation = xlCalculationManual    Application.EnableEvents = False        With ActiveSheet            .Unprotect        .Shapes("Button 1").Visible = False        lLastRow = .Range("E65536").End(xlUp).Row        For lCounter = 14 To lLastRow            If .Cells(lCounter, "E").Value = 1 Then                .Cells(lCounter, "E").EntireRow.Hidden = True                'Else: .Cells(lCounter, "E").EntireRow.Hidden = False            End If        Next lCounter        .Range("G12").Select        .Shapes("Button 2").Visible = True    End With    Application.EnableEvents = True    Application.Calculation = xlCalculationAutomatic    Application.ScreenUpdating = True    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=TrueEnd SubSub UnHideRow()    With ActiveSheet        .Unprotect        .Rows.Hidden = False        .Shapes("Button 1").Visible = True        .Shapes("Button 2").Visible = False        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True    End WithEnd Sub[/FONT][/COLOR]

 

[Fishboy]

Thank you so much! It's now working,

I think my latest code above will be best suited foe what you wanted.

now my next question is, is this going to work if I make a vba for copy sheet?

I'm sorry, I don't quite understand what you are asking

 

[Fishboy]

What's wrong with this code?

[COLOR=#373E4D][FONT=helvetica]Sub HideRow()Dim lLastRow As LongDim lCounter As Long    Application.ScreenUpdating = False    Application.Calculation = xlCalculationManual    Application.EnableEvents = False        With ActiveSheet            .Unprotect        .Shapes("Button 1").Visible = False        lLastRow = .Range("E65536").End(xlUp).Row        For lCounter = 14 To lLastRow            If .Cells(lCounter, "E").Value = 1 Then                .Cells(lCounter, "E").EntireRow.Hidden = True                'Else: .Cells(lCounter, "E").EntireRow.Hidden = False            End If        Next lCounter        .Range("G12").Select        .Shapes("Button 2").Visible = True    End With    Application.EnableEvents = True    Application.Calculation = xlCalculationAutomatic    Application.ScreenUpdating = True    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=TrueEnd SubSub UnHideRow()    With ActiveSheet        .Unprotect        .Rows.Hidden = False        .Shapes("Button 1").Visible = True        .Shapes("Button 2").Visible = False        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True    End WithEnd Sub[/FONT][/COLOR]

Wow, I'm really not too sure what is supposed to be happening here. I know trying to get it all on one line certainly isnt going to work, but I suspect that was the forums messing it up rather than how you originally had it. From what I can see there are 2 macros in here, a hide rows and an unhide rows.

Can you explain verbally exactly what you are expecting this to do, and in what order?

 

[jpbonono]

Wow, I'm really not too sure what is supposed to be happening here. I know trying to get it all on one line certainly isnt going to work, but I suspect that was the forums messing it up rather than how you originally had it. From what I can see there are 2 macros in here, a hide rows and an unhide rows.

Can you explain verbally exactly what you are expecting this to do, and in what order?

Sorry my bad, but there is just the same idea with what ive asked you it is just that i have this code before and i cannot be able to fix it.

Sub HideRow()Dim lLastRow As Long
Dim lCounter As Long


    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    With ActiveSheet
    
        .Unprotect
        .Shapes("Button 1").Visible = False


        lLastRow = .Range("E65536").End(xlUp).Row
        For lCounter = 14 To lLastRow
            If .Cells(lCounter, "E").Value = 1 Then
                .Cells(lCounter, "E").EntireRow.Hidden = True
                'Else: .Cells(lCounter, "E").EntireRow.Hidden = False
            End If
        Next lCounter
        .Range("G12").Select


        .Shapes("Button 2").Visible = True
    End With


    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True


    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True


End Sub


Sub UnHideRow()


    With ActiveSheet
        .Unprotect
        .Rows.Hidden = False
        .Shapes("Button 1").Visible = True
        .Shapes("Button 2").Visible = False
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End With


End Sub

 

[jpbonono]

This code was given to me by a friend, the problem is that everytime the button is pressed an error says "unlock the protected sheets first... blah blah".

 

[Fishboy]

This code was given to me by a friend, the problem is that everytime the button is pressed an error says "unlock the protected sheets first... blah blah".

Does the protection have a password? The code from your friend does not account for a password.

 

[jpbonono]

Does the protection have a password? The code from your friend does not account for a password.

He said that when he puts password it gives him an error while not when it is blank. Also he is afraid the the pre-locked cells will be unlocked by the time the macro is run.

 

[Fishboy]

He said that when he puts password it gives him an error while not when it is blank. Also he is afraid the the pre-locked cells will be unlocked by the time the macro is run.

If this implies that the workbook is protected but doesnt have a password that is as good as unprotected. If he is concerned that people will be able to access the formulas all they currently have to do is click the unprotect button and it is done. At least a password will prevent users from being able to do that.

This leads me on to your other point. If you can share any error messages you get in full, it will make it easier for us to work out why using a password is causing the error and allow us to try and resolve it.

 

[MARK858]

If this implies that the workbook is protected but doesn't have a password that is as good as unprotected. If he is concerned that people will be able to access the formulas all they currently have to do is click the unprotect button and it is done. At least a password will prevent users from being able to do that.

But that does take a deliberate action rather than being accidental which is in certain circumstances is a reason for protecting a worksheet without a password a bit like a "are you sure you want to do this" button (I won't go into whether it is worth password protecting a sheet).