Unprotecting macro button in a protected sheet

as the macro is working on the part of sheet which is protected

Click to expand...

There ya have it. You'll have to programatically unprotect the sheet at the beginning of the code, and then protect at the end.

jproffer said:

There ya have it. You'll have to programatically unprotect the sheet at the beginning of the code, and then protect at the end.

Click to expand...

Thanks jproffer. But as I do not know programming, I am not able to write the code in VB editer mode. So I am still stuck.

Open the editor and put this line at the beginning, just before the code that does stuff:


Then undo this at the end of the code with:

JasonC71 said:

Open the editor and put this line at the beginning, just before the code that does stuff:

Code:

Thisworkbook.Worksheets ("put the sheet name here").Unprotect ("password")

Then undo this at the end of the code with:

Code:

ThisWorkbook.Worksheets ("put sheet name here").Protect ("yourpassword")

Click to expand...

Thanks a lot. It works beautifully.

Hi,

I am having the same problem, but when I tried to use this code it kicked out as an invalid outside procedure. I am using Excel 2007. I need to unprotect the worksheet only to run the macro, but the macro itself kicks off every time the user clicks an unlocked cell.

Here is the code I am using to hide rows. I have a hidden column that contains formulas to generate a 0 if there is nothing in the data cells. That way the end users don't have to see things that don't pertain to them.

Any assistance would be fantastic. I am in a bit of a time crunch and I have been banging my head against the wall for the past week just to get this much to work. I am not a programmer.

Thank you.

Joe

P.S. I also tried creating a module for each and got the same result. Finally, do I also need to unprotect the workbook and if so, I will need help with that as well.





Sub HideZeroRows()
Dim w As Worksheet, a As Range, c As Range, s As String
s = "J11:J45" 'The range to check on each sheet
Application.ScreenUpdating = False
For Each w In ThisWorkbook.Worksheets
For Each a In Range(s)
For Each c In a
c.EntireRow.Hidden = (c.Value = 0)
Next c
Next a
Next w
Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
HideZeroRows
End Sub

try this after your "Sub HideZeroRows()"

ActiveSheet.Unprotect Password = "yourpassword"

and try this before your "End Sub"

ActiveSheet.Protect Password = "yourpassword"

Hope this works!

Thank you. That did the trick.

Hi there

I put the same code in for the macro, but now the autofilters on the sheet don't work.

Is the macro change related to the filters? I would not have thought so.

Thanks

JasonC71 said:

Open the editor and put this line at the beginning, just before the code that does stuff:

Code:

Thisworkbook.Worksheets ("put the sheet name here").Unprotect ("password")

Then undo this at the end of the code with:

Code:

ThisWorkbook.Worksheets ("put sheet name here").Protect ("yourpassword")

Click to expand...

Hello, everyone,
this unlocking and locking of the worksheet is great, but I want to add at the end, ie before the lock allows me to use the following things:
I tried to put them before, after, but when I pressed the specified button locks and does not allow me to filter as if these criteria were not set.
I ask for some assistance from you.
The worst thing is that I've put this lock and unlock in over 60 buttons and now I see that things are not happening.
Thank you in advance