Solved

Locking cells in Excel

Posted on 2014-04-27
10
200 Views
Last Modified: 2014-05-05
I was wondering if one had a row of say 10 cells. Is it possible to set it up so that the person filling out the row would not be able to write into a cell if there were empty cells previously.

So say, they typed the person's name in cell A2, then their phone number in cell B2. Then the person filling out the form tried to enter the street name in D2 before entering the cell phone in C2.

I would really like it if it shocked them with 50 volts, but I am rather sure that is a violation of the Federal Employee Regulations.

As you can probably figure out, I have an employee who doesn't know how or won't fill out an Excel sheet correctly. I suppose this is the last chance before firing them.
0
Comment
Question by:Bert2005
  • 4
  • 4
  • 2
10 Comments
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 175 total points
ID: 40026580
Put this in the Worksheet module:
Dim blnReset As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not blnReset Then
        If Target.Offset(, -1).Value = "" Then
            MsgBox "Darn"
            blnReset = True
            Target.Value = ""
        End If
    End If

End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40026954
Does it really matter what order the cells are completed so long as all 10 get completed?

Alternatively, set the sheet cursor movement so that when they press enter the cursor goes to the next cell to the right. When it gets to column 11 it checks if all cells are completed and if so it goes down a row and back to the beginning ready for next input; otherwise it goes back along the row to find the empty cell.

Thanks
Rob H
0
 
LVL 1

Author Comment

by:Bert2005
ID: 40028399
Thanks Macroshadow, I will give that a try.

Thanks Rob Henson  Yes, it does matter. The information needs to be entered in a particular sequence. I have tried to work with her for weeks. Cell #3 HAS to be used prior to Cell #4, but try as I might, Cells four, five, six, etc. are filled in first. Maybe if cells four, five, etc. can't be used, she will get the message.

I know. Should just let her go, but I am trying to give her every chance possible.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 40028405
I know. Should just let her go, but I am trying to give her every chance possible.
Good for you :-)
0
 
LVL 1

Author Comment

by:Bert2005
ID: 40028409
Thanks
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 31

Expert Comment

by:Rob Henson
ID: 40028444
You could set Data Validation on cell 2 to validate that cell 1 is not empty and cell 3 is empty cell 1 is A1 and validation on B1 would be:
 
=AND(A1<>"",C1="")

Thanks
Rob
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40028446
Copied across or down would move cell references accordingly.
0
 
LVL 1

Author Comment

by:Bert2005
ID: 40028547
Thanks Rob,

OK, so as you have noticed, I am very bad with Excel. Do you put the above in each cell or do you start with cell one and go to, say, cell 12. Maybe you could upload a picture?
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 325 total points
ID: 40028989
To implement Data Validation follow these few steps:

1) Select the required cells except for the first and last as these will need slightly different rule
2) Select the Data Ribbon Tab and select the Data Validation button
3) A small popup window will appear. On the settings tab there are two dropdowns only one of which is enabled by Default. From that Dropdown select "Custom" and a formula entry line will appear.
4) Enter the formula =AND(A1<>"",C1="") on the assumption that cell #1 is A1 and cell #3 is C1. Assuming this is B1, this will only allow an entry in B1 when A1 is already completed and C1 is still blank.

If you want to put a message that shows when the cell is selected by the user put this on the Input Message tab, if you want a message displayed when the user does something wrong (again!!) put this on the Error Alert tab, I don't think "Do it right or you're fired!" will go down too well though!!

5) Click OK

For the first and last cells, it is the same process but the formulas will be different:

Cell #1  =B1=""  (checks that the next cell is blank)
Cell #12 =K1<>"" (checks that the previous cell is not blank)

This all assumes that the 12 cells to be completed are cells A1:L1

Hope this helps.

Thanks
Rob H
0
 
LVL 1

Author Comment

by:Bert2005
ID: 40031081
So with this, it assures that basically the user must go through one cell at a time A1 through L1.

Generally, she will enter:

Consultant (cell 1) | Patient (cell 2) |       ****      (cell 3) | sent records (cell 4)

**** where (cell 3) is who called

The referral MUST be done in a certain order for optimal efficiency. She will skip cell 3 EVERY time even though it is there. She expects that faxing records to a consultant without any other communication with that office will result in a consultation. Obviously, without your being in medicine, you can probably still think of reasons to always call the consultant first:

Patient may be too old
Consultant may not accept the insurance
They may get the patient in sooner if they know how sick she is
Some will say, "we have an opening at 4 pm if they can get here."

One would think that having a numbered group of steps would be enough. I have said at least 10 times, when I message you the consult, you do three things. If the consultant is Dr. Jones and the patient is Steve Smith, you type Dr. Jones | Steve Smith | Spoke with Karen on phone |

So, maybe when she goes to type that she sent records, and she can't type it in, she will come get me.

Thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now