Running VBA codes while worksheets are protected.

[vlwaring]

Hi,

I have a workbook where each individual worksheet is password protected. Many of the codes involved are hiding row and columns and I want the code to be able to run, but I don't want the individual to be able to unhide columns/rows without the password.

Is there a way to do this.

 

[Andrew Poulsom]

Can't you unprotect then reprotect the worksheet in your VBA code?

 

[vlwaring]

How exactly do I do that? Is there a way to do that for the entire workbook?

This is what I have for one of the codes but it is not working:

Private Sub annual_Click()
If annual.Value = True Then
ActiveSheet.Unprotect Password:="vlwaring"
Rows("7:9").Hidden = True
Rows("10:11").Hidden = False
hourly.Value = False
ActiveSheet.Protect Password:="vlwaring"
End If
If annual.Value = False Then
ActiveSheet.Unprotect Password:="vlwaring"
Rows("10:11").Hidden = True
ActiveSheet.Protect Password:="vlwaring"
End If
End Sub

 

[Andrew Poulsom]

In what way isn't it working? What are annual and hourly?

 

[vlwaring]

These are checkboxes. When they are checked, certain rows are either shown or are hidden.

I get this message when I try to run the code:
The cell or chart that you are trying to change is protected and therefore read-only.
To modify a protected cell or chart, first remove protection using the unprotect sheet command (review tab, changes group. You may be prompted for a password.

When I press ok, it will show the lines that were hidden, but now my workbook is unprotected.

 

[Andrew Poulsom]

Where have you put the code you posted? You have qualified the Unprotect method with ActiveSheet, but not the Rows property.

 

[vlwaring]

It is in the active sheet. More specifically sheet4.

 

[HalfAce]

Hi,
I don't see Andrew around at the moment so perhaps this can help.
1) Your code is un-protecting and re-protecting the sheet regardless of the status of annual, so why not just do that at the beginning and the end of the routine.
2) You don't need to use two separate If statements for what you're doing, it could be done within an If, Else statement.
And... (3) you really don't need to wrap those in an If statement at all, you can simply assign the rows hidden property to compare with the status of your checkbox, 'annual'.
For example:

Private Sub annual_Click()

ActiveSheet.Unprotect Password:="vlwaring"

Rows("7:9").Hidden = annual.Value
Rows("10:11").Hidden = Not annual.Value
If annual Then hourly.Value = False

ActiveSheet.Protect Password:="vlwaring"

End Sub

As far as the functionality of it, as long as your password is correct, it should work for what I believe you're trying to do.

Does that help?

 

[vlwaring]

Thank you for the help, but I am still getting the same error message :/ Are you sure you are able to hide and unhide rows/columns using VBA when the worksheet?

 

[Andrew Poulsom]

What happens if you qualify the Rows property with ActiveSheet?