0

I am in a very strange situation in VBA. I am new to VBA and struggling since a couple of days in solving this problem.

I want to Lock the top three rows and the first three columns of my excel sheet for editing. However, these cells contain formulas, which I don't want the end user to edit and make changes. So basically, what I need is, the cells should perform all the operations, but should not be edited. How can, I do it in VBA. I am using Excel 2010.

One more thing, I have a button in the top rows ($B$2:$C$3) occupying four cells. This button is assigned to a macro which does the job of clearing contents in all the cells except the cell I Intend to lock. In short, in the other cells, the user can export data from a notepad and thereafter the formulas in the locked cell will do the job. When the user has to export new set of datas, he will cick on the button which is in ($B$2:$C$3), which clears the existing data and the user can imprt new set of dat from the notepad.

When I use

Worksheets("Sheet1").Range("B2:C3").Locked = False

Worksheets("Sheet1").Protect UserInterfaceOnly:=True

my button in ($B$2:$C$3) is not working properly. Any help from anybody will behighly solicited. Thanks a lot in advance.

Community
  • 1
  • 1
Shalom Vineeth
  • 35
  • 7
  • Kindly refer to this thread http://stackoverflow.com/questions/3037400/how-to-lock-the-data-in-a-cell-in-excel-using-vba –  Mar 28 '13 at 13:16
  • Thanks for the reply Santosh. But the problem is that my button is not working with that code. The above code which I have mentioned in the question is taken from that post. – Shalom Vineeth Mar 28 '13 at 13:29
  • When you say the button doesn't work, what do you mean? Also, are you applying the Protection with UserInterFaceOnly every time you open the workbook? – Doug Glancy Mar 28 '13 at 13:39
  • i will post the screenshot for you. –  Mar 28 '13 at 13:42
  • When I say the button doesn't work, it cannot clear the contents in other cells, which it is supposed to do. I will post a screenshot of the worksheet for better understanding... – Shalom Vineeth Mar 28 '13 at 13:46
  • 1
    Again, I wonder if you are protecting with UserInterFaceOnly every time you open the workbook? That setting doesn't persist after the workbook is closed. – Doug Glancy Mar 28 '13 at 13:48
  • Yes I am using the UserInterFaceOnly.. Is there any other option to do that..??? Btw, I am not able to post the image, since I do not have enough points.. Apparently I need to have at least 010 points to post..... – Shalom Vineeth Mar 28 '13 at 13:55
  • in your Workbook_Open event, do you have the code **Worksheets("Sheet1").Protect UserInterfaceOnly:=True** ? – Our Man in Bananas Mar 28 '13 at 14:01

1 Answers1

0

try this:

Worksheets("Sheet1").Range("B2:C3").Locked = true

Worksheets("Sheet1").Protect UserInterfaceOnly:=True

This should fix the problem.

vba_user111
  • 215
  • 1
  • 15
Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148