Locking cells using a macro

[m0llydog]

Hello again,

I hope someone can help with this query.

What I am building is a form using Excel which will be completed by many users and for data integrity purposes I have to build in as many safeguards as possible to prevent "wrong" data being entered.

I have a few cells (J18:N18) which are auto populated if "VC" is entered into cell I18.

What I would like to do is lock these cells if VC is entered so that the data cannot be overwritten as long as VC is still in the cell I18.

If VC is removed from I18 then the data in cells J18:N18 will disappear because of the auto population formula I have in these cells and the cells should again be available for manual completion.

Can this be done? If so, be grateful for a suggestion as to the type of macro which would allow this to happen.

Thanks again

[Leith Ross]

Hello M0llydog,

All cells on the worksheet are locked by default. Locking doesn't work until the worksheet is protected. So, the first thing to do is Unlock cell I18.
1) Select the cell.
2) Press ALT+0 (Letter not zero)
3) Type e
4) Press ALT+ L (The checkbox labeled Locked should clear)

Next copy the code below using CTRL+C. Right Click on the worksheet's tab at the bottom. Click "View Code" and then use CTRL+P to paste the macro into the workbook. Press CTRL+S to save the macro.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[m0llydog]

Thanks for the suggestion however I omitted some data from my original post.

The sheet is already protected with a password, will this macro conflict with or change that protection in any way?

[m0llydog]

Sorry more data and further questions

There are many other cells on the workbook which are not protected to allow data entry, will these still be available after this macro is activated by a VC entry in I18.

In addition I18 is a representative cell which repeats itself many times down the form (potentially hundreds of lines will have the functionality of this cell) but they will alwaus be in the relative position occupied by I18, i.e. J18, K18, L18 etc.

Sorry for missing all this out of my original post.

[Leith Ross]

Hello M0llydog.

I can answer 2 of your questions. The macro will not affect the cells that are unlocked, and your password can be included in the macro. I chose the default of no password since you did not indicate otherwise. Insert your password in the code where it is marked in red. Be sure to enclose it in quotes.

Please Login or Register  to view this content.

As for the rest of your request, I am not sure you have all the details thought out. When you do, please post a sample of how the data will appear on the worksheet or post a picture of the worksheet.

Sincerely,
Leith Ross