Lock Specific Cells while still allowing MACRO Searches, Filters and Sorting

[ghp2017]

Hello,

I have a spreadsheet that we using for inventory. To assist the counters I created buttons/macros to assist them with filtering and searching. There are a number of cells that I want to protect but when I do the buttons/macros do not work either. Does anyone know how to get around this? I can't get around the following message:

Microsoft Visual Baic

Run-time error '1004':

You cannot use this command on a protected sheet. To use this command, you must first unprotect the sheet...

 

[tuple1020]

Good morning!

Unfortunately macros still get stuck like how a real person would when you lock & protect cells, lock workbooks, hide sheets, etc. Think of a macro not as an all knowing machine, but like a real person who's just doing what you want it to you (which makes your life easier.)

The way how I get around situations like this is have the macro unlock the protected sheet at the beginning of the coding, then lock it again at the end, making sure the workbook is saved after it's locked again.
The way how this might look in coding could be:

Sub example()

'You can change the 123 to whatever password you want.
Sheets(1).Unprotect Password:="123"

'
'Do whatever you want the macro to do here....
'

Sheets(1).Protect Password:="123"
Activeworkbook.Save

Hope this helps!!

 

[ghp2017]

Good morning!

Unfortunately macros still get stuck like how a real person would when you lock & protect cells, lock workbooks, hide sheets, etc. Think of a macro not as an all knowing machine, but like a real person who's just doing what you want it to you (which makes your life easier.)

The way how I get around situations like this is have the macro unlock the protected sheet at the beginning of the coding, then lock it again at the end, making sure the workbook is saved after it's locked again.
The way how this might look in coding could be:

Sub example()

'You can change the 123 to whatever password you want.
Sheets(1).Unprotect Password:="123"

'
'Do whatever you want the macro to do here....
'

Sheets(1).Protect Password:="123"
Activeworkbook.Save

Hope this helps!!

---

This is perfect. There's just one thing. After I use a button/macro I am no longer able to use the default auto filter/drop-down list selection.

 

[tuple1020]

Give this a try:

Sheets(1).Protect Password:="123", AllowFiltering:=True

But when unprotecting, still use:
Sheets(1).Unprotect Password:="123"

 

[ghp2017]

Give this a try:

Sheets(1).Protect Password:="123", AllowFiltering:=True

But when unprotecting, still use:
Sheets(1).Unprotect Password:="123"

---

Awesome, thanks. Will try it now.