Locking & Unlocking Cells to Protect Data in Excel

Hi All,

I am not too sure if this requirement can be achieved with or without automation, so I thought to put this query under Excel Programming. I am designing an Excel Interface (not a form, just a layout in Excel Worksheet). In this layout, user will need to enter some information. Now I want to set up my worksheet in such a way, that the user should be able to select only cells which are required to provide the inputs. User should not be able to select any cells other than the input cells even. Is this possible? I know we can lock the cells but it doesn't stop user to select any other cells. Can someone help please?

Thanks,
Vikas

Hi All

I have a button which clears the contents of cell B8 after which I would like to lock the range B10:B35. I have tried the code below which will successfully unlock the sheet User_Interface, clear the contents of B8 but doesn't then lock the range (B10:B35)

Sub Unlock()
ActiveSheet.Unprotect Password:="googlemaps"
Sheets("User_Interface").Range("B8").ClearContents
ActiveSheet.Protect Range("B10:B35)") Password:="googlemaps"
End Sub

Any help on this is much appreciated

Regards
Donal

How can I go about coding a cell to be locked / unlocked?

Hi All,

I'm basically the middle person for a spreadsheet that I'm creating.

The process that will be used is that, I would set up the sheet every month and send it to a data entry person. Then they would send it back to me, and i would send it to certain people for approval. Once they respond with approval I then send it back to the data entry person for filing.

I'm hoping to create a button that will "flip" a switch to to speak.

When I send it to the data entry person the first time, I need certain cells locked and unlocked.

When I send it to the people for approval I need the whole thing locked as well as for when I send it back to the data entry person for filing.

I know how to do this manually, but my issue is that every time I need to send it back to the data entry person at the beginning of the month I have to go over every data entry cell and set it up so that when I lock the sheet they stay unlocked.

So that's where the button would come in...it could even be 2 buttons...Data Entry Locking and then a full Sheet Locking...I would need each password protected too.

Does this make any sense...hopefully it does...I've been working on this spreadsheet forever!!!

Thanks,
No.Solutions.

I know there has to be a way to clean this up into 1 command instead of selection each cell and locking/unlocking. Any help would be appreciated.

For Each cell In Range("RBARECT_LINE").Cells
If cell.Value "" Then
cell.Offset(0, 3).Select
Selection.Locked = False
cell.Offset(0, 4).Select
Selection.Locked = False
cell.Offset(0, 5).Select
Selection.Locked = False
cell.Offset(0, 6).Select
Selection.Locked = False
cell.Offset(0, 11).Select
Selection.Locked = False
cell.Offset(0, 13).Select
Selection.Locked = False
Else
cell.Offset(0, 3).Select
Selection.Locked = True
cell.Offset(0, 4).Select
Selection.Locked = True
cell.Offset(0, 5).Select
Selection.Locked = True
cell.Offset(0, 6).Select
Selection.Locked = True
cell.Offset(0, 11).Select
Selection.Locked = True
cell.Offset(0, 13).Select
Selection.Locked = True
End If
Next cell

I know there is another thread about this. However, I was wondering :

if in a range of cells say (a4:k45)

Assume all cells are locked.

If a cell is highlighted yellow to begin with I want it to be unlocked....

there are multiple cells throughout the range that will be highlighted. I want them all unlocked....

And when they are unhighlighted I want them to be relocked.

is this possible?

I need to create a grid that displays and accepts data based on a rolling 14-days, seven days prior to Report Date and seven days following Report Date.

The data is displayed in columns by date. For any column where the date is not in the same calendar month as the Report Date, I want to "grey" out and lock the cells, preventing data entry.

How would I code a VB loop to accomplish this?

I have attached a small worksheet showing what I am tryiny to accomplish.

Thanks,
SSGMiami

Hello,

I want to create a table in excel,such that

if there are 3 columns(a,b,c) then

if data is entered in cells of column A then cells in column's B and c should be locked.

If data in cells of column A is deleted or if the cell's in A are empty then columns B and C should be unlocked.

can some one tell me how to do this.

Worksheet is protected. In my worksheet I have cells that gather data from user input. I would like to show less clutter in the worksheet and easier navigation when user is using arrow or tab key to go from cell to cell. Example: there is an address area, and there are two other areas such as mailing address and billing address. If I left the mailing address area and billing address area unlocked then the user would have to tab or arrow their way multiple times to get to the next required area. Since address contains house number, street and city and phone number it creates unnecessary tabs. I want to lock those cells so they are only unlocked when you click a button (mailing address). This way the user only unlock those cells when it is required. It will save many extra tabs to get to where they need to go. What if the protected sheet is password protected? How do I unprotect the sheet with the password in the background, unlock those cells, then protect the sheet again with the same password all without showing the user the password?

Thanks

Afternoon all,

This is what I want to happen but I have no idea how to do it.

If C14 = "IV" THEN E14 = Locked AND F14 = Unlocked
If C14 = "RV" THEN E14 = Unlocked AND F14 = Locked
If C14 = "AJ" THEN E14 AND F14 = Unlocked

Another thing I would like to make possible is for this to work throughout cells C14:C450, E14:E450 and F14:F450

Can anyone help me out on this?