In Excel if a particular cell is empty how can I keep users from entering data in 2 other fields

I have an excel worksheet with 13 columns.  I wanted to know how via VBA I could keep the user from entering data in 2 cells, if another specific cell is empty.

Empty Column/Cell:  InvElecHistoryID

2 Columns/Cell [DateSent] and [Comments] that data can't be entered into unless the Empty Column above has data.

Open in new window


Thanks,

gdunn59
LVL 1
gdunn59Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aranaCommented:
you can protect and unprotect a cell depending another cell content

Sheet1.Unprotect
If Len(Range("A1").Formula) = 0 then
         Sheet1.Range("A4", "A5").Locked = False 'Unlock the range A4 to A5
            Sheet1.Cells(6, 6).Locked = False 'Unlock the cell F6
            Sheet1.Protect
end if
gdunn59Author Commented:
arana,

Where would I put the code?

Thanks,
gdunn59
aranaCommented:
You can put it in the sheetselectionchange event of the worksheet
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

aranaCommented:
keep in mind that once you protect a worksheet EVERY CELL by default is locked, so you must change the protetion property to unlocked (under the format cells tab) for each cell that you do not want to be locked.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Right click the Sheet Tab --> View Code and paste the following code into the opened code window --> Save workbook as Macro-Enabled Workbook.

The code assumes that the InvElecHistoryID is the column A header and user is supposed to fill the info in column A first and then only user is allowed to fill the DateSent and Comments columns.
If user doesn't fill the InvElecHistoryID and tries to select the cell in either DateSent and Comments columns, user will be prompted to fill the InvElecHistoryID first and then fill these columns and code will select the corresponding InvElecHistoryID cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

'2 represents column B and 5 represents column E in the below line. Change it as per your requirement.
If Target.Column = 2 Or Target.Column = 5 And Target.Row > 1 Then
   'Assuming InvElecHistoryID is column A header
   If Cells(Target.Row, "A") = "" Then
      MsgBox "Enter the InvElecHistoryID first and then continue...", vbExclamation, "InvElecHistoryID Missing!"
      Cells(Target.Row, "A").Select
   End If
End If
End Sub

Open in new window

For details, refer to the attached.
Validate-Empty-Cell.xlsm
Roy CoxGroup Finance ManagerCommented:
I would protect the sheet, allowing selection of both Locked and Unlocked cells. First make sure all other input cells are not Locked.

Then assuming a password "Secret", and the ID is in Column A

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'''/// substitute your password here
    Const PW As String = "Secret"

    With Target
        ''/// only works if one cell is selected
        If .Count > 1 Then Exit Sub
        ''/// if an error occurs make sur the protection is reset
        On Error GoTo err_quit
        ''///unprotect the sheet (the keyword Me refers to the sheet
        Me.Unprotect PW
        ''/// triggers code if cell is in A or E and not the header row
        If .Column = 2 Or .Column = 5 And .Row > 1 Then
            ''/// ID in A
            If IsEmpty(Me.Cells(.Row, 1)) Then
                MsgBox "You must enter the InvElecHistoryID before you can input to this cell", vbCritical, "Input Required"
            Else:
                ''/// ID is entered allow input
                Me.Cells(.Row, 2).Locked = False
                Me.Cells(.Row, 5).Locked = False
            End If
        End If
    End With
    ''/// restore protection
err_quit:
    Me.Protect PW
End Sub

Open in new window


Password Protect Sheet
Rob HensonFinance AnalystCommented:
I think you could use Data Validation with a custom rule, so wouldn't need VBA.
gdunn59Author Commented:
Rob Henson,

I tried the Data Validation, but I can't seem to get it to work.

Can you elaborate?

Thanks,

gdunn59
aranaCommented:
so gdunn59 have you tried any of the above ?
Roy CoxGroup Finance ManagerCommented:
Give Rob a chance to get back with his suggestion.
Roy CoxGroup Finance ManagerCommented:
The formula for data validation would be

=NOT(ISBLANK(A2)) with a
gdunn59Author Commented:
Arana,

Yes I tried your solution and sktneer's solution, and neither of them worked (nothing happened).  It still allowed me to enter data in the 2 cells (L & M) even though the other cell (F) was null/empty.

I apologize if I didn't make it real clear, when a user opens the spreadsheet it already contains data.  Most of the cells contain data, but what I need to check is if the one cell (Column F, starting at Row 20) is empty they cannot update the other 2 cells (Columns L & M, starting at Row 20).  So it just needs to stop the user from being able to update Cells(Columns) L & M, if Cell(Column) F is null/empty.  Also, I want a MsgBox to pop up if F is empty alerting them that they can't update L or M because F is null/empty.

I just wanted to try the Data Validation thinking it might be easier.

Thanks,

gdunn59
Roy CoxGroup Finance ManagerCommented:
Formula
msg
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I am really not sure what didn't work for you.
Of course you were required to change the column and row references in the code as you didn't gave any clue in your original description.

Pls find the attached workbook with the modified code and see if this works for you.
Validate-Empty-Cell.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Have you tried my code, simply change the columns to check
gdunn59Author Commented:
Roy_Cox,

I've never used the Data Validation in Excel, most of my expertise is in Access.

I'm not sure how to go about adding the validation.  What I tried (but it didn't work), is I selected the 2 columns that a user might try to update, then I chose Data Validation and entered the information in the 2 screen shots that you provided, then clicked okay.

But when I went to one of the two cells and entered data, nothing happens if I entered data in one of those 2 cells where the other cell is empty.

What am I doing wrong?

Thanks,

gdunn59
Roy CoxGroup Finance ManagerCommented:
Here's a simple example. The yellow cell has the Data Validation. To view the Data Validation select the cell and click data validation in the Data Tab of the Ribbon.

I'll check later but you will find plenty online about Data validation such as this

Data Validation will obviously not require macros to be enabled or explained.
DV-Blank-Cell.xlsx
gdunn59Author Commented:
Roy_Cox,

I tried your data validation, and it doesn't prevent me from entering data if the first cell is empty.  I even downloaded your spreadsheet and tried it and it does nothing.

Thanks,

gdunn59
gdunn59Author Commented:
sktneer,

Your solution worked, but I was curious what this line of code is doing, because when I tried it I got an overflow error, but I removed it and the code works fine:

'If Target.Count > 1 Then Exit Sub

Thanks,
gdunn59
Roy CoxGroup Finance ManagerCommented:
It was working, I'll check later
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
There is a limitation with Data Validation that if a value is pasted over the cell with data validation, it won't issue any warning of invalid input.

You don't need to remove that line of code, it is there to check if multiple cells have been selected at once.
gdunn59Author Commented:
sktneer,

As I mentioned above, I got a "run-time error 6 - overflow" error, but I removed it the code worked fine:

 'If Target.Count > 1 Then Exit Sub

Thanks,
gdunn59
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Better if there is any chance for selecting the whole sheet, replace that line with the below one.
If Target.CountLarge > 1 Then Exit Sub
gdunn59Author Commented:
sktneer,

Everything works perfect now.

Here is the final code I used:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub

If Target.Column = 12 Or Target.Column = 13 And Target.Row > 7 Then
   If Cells(Target.Row, "F") = "" Then
      MsgBox "You cannot update SubmittedDate or Comments2 because InvElecHistoryID does not contain a value.", vbExclamation, "InvElecHistoryID Missing!"
      Cells(Target.Row, "F").Select
   End If
End If
End Sub

Open in new window


Thanks much,

gdunn59


P.S.  Thank you everyone else also for your input and time!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad your issue resolved ultimately.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.