M. Saad
asked on
Protect cells contain formulas and keep Macro running
Hello
Can you please help with the attached file if possible.
The attached file is macro enabled file and I need to protect all cells contain formulas ( highlighted in yellow color ) and any additional cells will contain formulas in future too.
i tried to protect the sheet but the Macro will not work unless I remove the protection.
Any ideas how to protected the cells without and keep the macro working in same time.
Filter-Test.xlsm
Can you please help with the attached file if possible.
The attached file is macro enabled file and I need to protect all cells contain formulas ( highlighted in yellow color ) and any additional cells will contain formulas in future too.
i tried to protect the sheet but the Macro will not work unless I remove the protection.
Any ideas how to protected the cells without and keep the macro working in same time.
Filter-Test.xlsm
ASKER
thanks for your reply , i already tried to do this method but i found the cells will not be protected during the process ( unprotected and then re protect ).
i need to keep all formulas cells protected all time for all users and remove protection only by me via password
i need to keep all formulas cells protected all time for all users and remove protection only by me via password
The protection should be restored immediately after protecting, they need to be unprotected during the filter. When the macro is running there will be no manual access to the sheets
I've added the userinterface code for you. Try this, it works for me
Filter-Test.xlsm
Filter-Test.xlsm
ASKER
thanks a lot for your code, after i tested have small issue
i have used the macro bottom " Show Open " and excel show me result for 31 rows.
next i wanted to filter these 31 rows from column F " Assigned leader " but i was not able to do that.
i have changed the protection criteria by allowing the user to use auto filter , sort , then save with same password " Secret" and it was working.
then i closed the file and re-open again but again i was not able to use any filter within the table itself ( row 6 ).
any advise please
i have used the macro bottom " Show Open " and excel show me result for 31 rows.
next i wanted to filter these 31 rows from column F " Assigned leader " but i was not able to do that.
i have changed the protection criteria by allowing the user to use auto filter , sort , then save with same password " Secret" and it was working.
then i closed the file and re-open again but again i was not able to use any filter within the table itself ( row 6 ).
any advise please
Can you attach your file.
Have you tried my file?
Have you tried my file?
To protect a sheet but allow macros to run, protect the sheet through code like this
Sheets("Sheet1").Protect UserInterfaceOnly:=True
Sheets("Sheet1").Protect UserInterfaceOnly:=True
Hi Martin
My example file uses that
My example file uses that
Sorry, I didn't realize that.
No problem, I don't always check others' files.
ASKER
dear Roy
apologize for late reply , i lost the connection . i have sent you message with original file. kindly check it when you can.
thank a lot
apologize for late reply , i lost the connection . i have sent you message with original file. kindly check it when you can.
thank a lot
Further to your message I have updated your private file with this code to allow macros to run and allow autofilter
Add protection , place in Workbook_open event
Updated your filter code because you are Using Tables.
Add protection , place in Workbook_open event
Private Sub Workbook_Open()
''/// this code will protect all Worksheets in the WorkBook
' Dim oWs As Worksheet
' For Each oWs In Worksheets
' ''/// you can change the password here by chnaging "Secret" to anything you want
' oWs.Protect Password:="Secret", UserInterFaceOnly:=True
' Next oWs
''/// to protect individual sheets use the code like this
Worksheets("Overall").Protect Password:="Secret", DrawingObjects:=True, _
contents:=True, Scenarios:=True, AllowFiltering:=True, _
userinterfaceonly:=True
End Sub
Updated your filter code because you are Using Tables.
Sub Overdue()
'
' Overdue Macro
'
On Error GoTo exit_Proc
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Overall_data").Range.AutoFilter Field:=18, _
Criteria1:="overdue"
exit_Proc:
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
Sub allOpen()
'
' allOpen Macro
'
On Error GoTo exit_Proc
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Overall_data").Range.AutoFilter Field:=19, _
Criteria1:="open"
exit_Proc:
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
Sub ShowAllRecords()
''/// you are using a Table so the Filter must be removed like this
On Error GoTo exit_Proc
Application.ScreenUpdating = False
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
exit_Proc:
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
many thanks for your big efforts Mr. Roy
file is working like charm
regards
file is working like charm
regards
Pleased to help
Open in new window
You can protect using UserInterface only
Excel-UserInterfaceOnly.docx