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

gdunn59
gdunn59 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
arana,

Where would I put the code?

Thanks,
gdunn59
You can put it in the sheetselectionchange event of the worksheet
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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 Manager

Commented:
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 Analyst

Commented:
I think you could use Data Validation with a custom rule, so wouldn't need VBA.

Author

Commented:
Rob Henson,

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

Can you elaborate?

Thanks,

gdunn59
so gdunn59 have you tried any of the above ?
Roy CoxGroup Finance Manager

Commented:
Give Rob a chance to get back with his suggestion.
Roy CoxGroup Finance Manager

Commented:
The formula for data validation would be

=NOT(ISBLANK(A2)) with a

Author

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 Manager

Commented:
Formula
msg
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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
Roy CoxGroup Finance Manager

Commented:
Have you tried my code, simply change the columns to check

Author

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 Manager

Commented:
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

Author

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

Author

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 Manager

Commented:
It was working, I'll check later
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome. Glad your issue resolved ultimately.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial