Unprotecting macro button in a protected sheet

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
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

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
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

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
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

Board Regular
Joined
Feb 10, 2008
Messages
159
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")
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
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")

Thanks a lot. It works beautifully.
 

JoeXoft

New Member
Joined
Aug 26, 2008
Messages
5
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
 
Last edited:

jerik_sean

New Member
Joined
Oct 8, 2008
Messages
18
try this after your "Sub HideZeroRows()"

ActiveSheet.Unprotect Password = "yourpassword"

and try this before your "End Sub"

ActiveSheet.Protect Password = "yourpassword"

Hope this works!
 

alekche

New Member
Joined
Mar 23, 2011
Messages
2
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

Active Member
Joined
Feb 8, 2012
Messages
307
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")
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:
Code:
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
 

Forum statistics

Threads
1,082,250
Messages
5,364,022
Members
400,774
Latest member
Goldi paul

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top