lock cells for edit without using sheet protection

**jimjaix** · 03-10-2009, 03:08 PM

Hi, I want to know if there's any ways I can lock certain cells in an excel sheet without using the sheet protection. The reason why I don't use the sheet protection is because when I enable it, my command button and popup calendar doesn't work. I am looking for a vba code that allow me to lock certain cells, the cells contain formula and there's no need to edit them. thanks

**shg** · 03-10-2009, 03:18 PM

You cannot protect cells other than by protecting the sheet. Properly implemented, sheet protection shouldn't bother command buttons or your pop-up calendar.

**jimjaix** · 03-10-2009, 03:28 PM

Hi, maybe you can help me out with protecting the sheet, this is the code for my command button and it sorts all the rows, maybe that's the problem?

Please Login or Register  to view this content.

When I debug it points to the oRange.Sort Key1:=Range("B4"), Order1:=iOrder, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

**royUK** · 03-10-2009, 03:38 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**Leith Ross** · 03-10-2009, 08:14 PM

Hello jimjaix,

You should check the value of "iOrder". It is probably zero. You declare it a Static Integer, but don't assign it an initial value. When tested in the If...Then, there it never changes because it is zero and doesn't match either of the Excel sorting constants.

**shg** · 03-10-2009, 08:36 PM

You code looks fine. iOrder will be set to xlAscending in the first iteration and toggle thereafter.

At the beginning of the code, add this line:

Please Login or Register  to view this content.

BTW, what is the error message you get?

**jimjaix** · 03-11-2009, 10:13 AM

Tried that code it didn't work, the error message I am getting once I protect the sheet and use the sort button is "Run-Time error '1004', The cell or chart you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove the protection using the unprotect sheet command. you may be promoted for a password."

Originally Posted by shg

You code looks fine. iOrder will be set to xlAscending in the first iteration and toggle thereafter.

At the beginning of the code, add this line:

Please Login or Register  to view this content.

BTW, what is the error message you get?

**royUK** · 03-11-2009, 10:15 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**jimjaix** · 03-11-2009, 10:32 AM

I attached the file. Hope this will help...

Originally Posted by royUK

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!