Unprotecting macro button in a protected sheet

[asad]

I have made a sheet that I have to give access to other users as well. But I have protected cells in the sheet so that the formulas can not be altered. But when I do this, macro button stop responding. In fact, they become unclickable. I tried moving the buttons to the unprotected cells but as the macro is working on the part of sheet which is protected, so it is not working.
I want user to be able to click on macro button and have access to some cells of the sheet but the rest of sheet should stay protected.

Can somebody help me please?

 

[jproffer]

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

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

 

[asad]

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

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.

 

[JasonC71]

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

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

Then undo this at the end of the code with:

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

 

[asad]

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

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

Then undo this at the end of the code with:

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

Thanks a lot. It works beautifully.

 

[JoeXoft]

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

 

[jerik_sean]

try this after your "Sub HideZeroRows()"

ActiveSheet.Unprotect Password = "yourpassword"

and try this before your "End Sub"

ActiveSheet.Protect Password = "yourpassword"

Hope this works!

 

[JoeXoft]

Thank you. That did the trick.

 

[alekche]

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

 

[K0st4din]

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

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

Then undo this at the end of the code with:

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

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:

Sub Dublikati_psix_J()

ThisWorkbook.Worksheets("psix").Unprotect ("my password")

' here is my code

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:= _
        True
    ActiveSheet.EnableSelection = xlNoRestrictions


    ThisWorkbook.Worksheets("psix").Protect ("my password")

End Sub

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