<div>
you can protect and unprotect a cell depending another cell content<br />
<br />
Sheet1.Unprotect<br />
If Len(Range(&quot;A1&quot;).Formula) = 0 then<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sheet1.Range(&quot;A4&quot;, &quot;A5&quot;).Locked = False 'Unlock the range A4 to A5<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sheet1.Cells(6, 6).Locked = False 'Unlock the cell F6<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sheet1.Protect<br />
end if
</div>


<div>
arana,<br />
<br />
Where would I put the code?<br />
<br />
Thanks,<br />
gdunn59
</div>


<div>
You can put it in the sheetselectionchange event of the worksheet
</div>


<div>
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.
</div>


<div>
Right click the Sheet Tab --&gt; View Code and paste the following code into the opened code window --&gt; Save workbook as Macro-Enabled Workbook.<br />
<br />
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.<br />
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.<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-41480148-1"><span>Private Sub Worksheet_SelectionChange(ByVal Target As Range)</span>
<span>If Target.Count &gt; 1 Then Exit Sub</span>
<span></span>
<span>'2 represents column B and 5 represents column E in the below line. Change it as per your requirement.</span>
<span>If Target.Column = 2 Or Target.Column = 5 And Target.Row &gt; 1 Then</span>
<span>   'Assuming InvElecHistoryID is column A header</span>
<span>   If Cells(Target.Row, &quot;A&quot;) = &quot;&quot; Then</span>
<span>      MsgBox &quot;Enter the InvElecHistoryID first and then continue...&quot;, vbExclamation, &quot;InvElecHistoryID Missing!&quot;</span>
<span>      Cells(Target.Row, &quot;A&quot;).Select</span>
<span>   End If</span>
<span>End If</span>
<span>End Sub</span>
</code></pre>
For details, refer to the attached.<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/02_w09/1083313/Validate-Empty-Cell.xlsm" target="_blank" class="file-inline" title="Validate-Empty-Cell.xlsm (14 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Validate-Empty-Cell.xlsm</a>
</div>


Validate-Empty-Cell.xlsm

<div>
I would protect the sheet, allowing selection of both Locked and Unlocked cells. First make sure all other input cells are not Locked.<br />
<br />
Then assuming a password &quot;Secret&quot;, and the ID is in Column A<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-41480229-1"><span>Option Explicit</span>
<span></span>
<span></span>
<span>Private Sub Worksheet_SelectionChange(ByVal Target As Range)</span>
<span>'''/// substitute your password here</span>
<span>    Const PW As String = &quot;Secret&quot;</span>
<span></span>
<span>    With Target</span>
<span>        ''/// only works if one cell is selected</span>
<span>        If .Count &gt; 1 Then Exit Sub</span>
<span>        ''/// if an error occurs make sur the protection is reset</span>
<span>        On Error GoTo err_quit</span>
<span>        ''///unprotect the sheet (the keyword Me refers to the sheet</span>
<span>        Me.Unprotect PW</span>
<span>        ''/// triggers code if cell is in A or E and not the header row</span>
<span>        If .Column = 2 Or .Column = 5 And .Row &gt; 1 Then</span>
<span>            ''/// ID in A</span>
<span>            If IsEmpty(Me.Cells(.Row, 1)) Then</span>
<span>                MsgBox &quot;You must enter the InvElecHistoryID before you can input to this cell&quot;, vbCritical, &quot;Input Required&quot;</span>
<span>            Else:</span>
<span>                ''/// ID is entered allow input</span>
<span>                Me.Cells(.Row, 2).Locked = False</span>
<span>                Me.Cells(.Row, 5).Locked = False</span>
<span>            End If</span>
<span>        End If</span>
<span>    End With</span>
<span>    ''/// restore protection</span>
<span>err_quit:</span>
<span>    Me.Protect PW</span>
<span>End Sub</span>
</code></pre>
<br />
<a href="https://support.office.com/en-us/article/Password-protect-worksheet-or-workbook-elements-dbf706e0-ba22-4a08-84d8-552db16eef11" rel="ugc">Password Protect Sheet</a>
</div>


<div>
I think you could use Data Validation with a custom rule, so wouldn't need VBA.
</div>


<div>
Rob Henson,<br />
<br />
I tried the Data Validation, but I can't seem to get it to work.<br />
<br />
Can you elaborate?<br />
<br />
Thanks,<br />
<br />
gdunn59
</div>


<div>
so gdunn59 have you tried any of the above ?
</div>


<div>
Give Rob a chance to get back with his suggestion.
</div>


<div>
The formula for data validation would be <br />
<br />
=NOT(ISBLANK(A2)) with a
</div>


<div>
Arana,<br />
<br />
Yes I tried your solution and sktneer's solution, and neither of them worked (nothing happened). &nbsp;It still allowed me to enter data in the 2 cells (L &amp;&nbsp;M) even though the other cell (F) was null/empty.<br />
<br />
I apologize if I didn't make it real clear, when a user opens the spreadsheet it already contains data. &nbsp;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 &amp;&nbsp;M, starting at Row 20). &nbsp;So it just needs to stop the user from being able to update Cells(Columns) L &amp;&nbsp;M, if Cell(Column) F is null/empty. &nbsp;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.<br />
<br />
I just wanted to try the Data Validation thinking it might be easier.<br />
<br />
Thanks,<br />
<br />
gdunn59
</div>


DV-Msg.png

DV-Formula.png

<div>
Have you tried my code, simply change the columns to check
</div>


<div>
Roy_Cox,<br />
<br />
I've never used the Data Validation in Excel, most of my expertise is in Access.<br />
<br />
I'm not sure how to go about adding the validation. &nbsp;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.<br />
<br />
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.<br />
<br />
What am I doing wrong?<br />
<br />
Thanks,<br />
<br />
gdunn59
</div>


<div>
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.<br />
<br />
I'll check later but you will find plenty online about Data validation such as <a href="https://support.office.com/en-us/article/Apply-data-validation-to-cells-c743a24a-bc48-41f1-bd92-95b6aeeb73c9" rel="ugc">this</a><br />
<br />
Data Validation will obviously not require macros to be enabled or explained.<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/02_w09/1083417/DV-Blank-Cell.xlsx" target="_blank" class="file-inline" title="DV-Blank-Cell.xlsx (13 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>DV-Blank-Cell.xlsx</a>
</div>


DV-Blank-Cell.xlsx

<div>
Roy_Cox,<br />
<br />
I tried your data validation, and it doesn't prevent me from entering data if the first cell is empty. &nbsp;I even downloaded your spreadsheet and tried it and it does nothing.<br />
<br />
Thanks,<br />
<br />
gdunn59
</div>


<div>
sktneer,<br />
<br />
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:<br />
<br />
'If Target.Count &gt;&nbsp;1 Then Exit Sub<br />
<br />
Thanks,<br />
gdunn59
</div>


<div>
It was working, I'll check later
</div>


<div>
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.<br />
<br />
You don't need to remove that line of code, it is there to check if multiple cells have been selected at once.
</div>


<div>
sktneer,<br />
<br />
As I mentioned above, I got a &quot;run-time error 6 - overflow&quot; error, but I removed it the code worked fine:<br />
<br />
&nbsp;'If Target.Count &gt;&nbsp;1 Then Exit Sub<br />
<br />
Thanks,<br />
gdunn59
</div>


<div>
sktneer,<br />
<br />
Everything works perfect now.<br />
<br />
Here is the final code I used:<br />

<pre><code class="code-10 nolinks" id="code-20-41481259-1"><span>Private Sub Worksheet_SelectionChange(ByVal Target As Range)</span>
<span>If Target.CountLarge &gt; 1 Then Exit Sub</span>
<span></span>
<span>If Target.Column = 12 Or Target.Column = 13 And Target.Row &gt; 7 Then</span>
<span>   If Cells(Target.Row, &quot;F&quot;) = &quot;&quot; Then</span>
<span>      MsgBox &quot;You cannot update SubmittedDate or Comments2 because InvElecHistoryID does not contain a value.&quot;, vbExclamation, &quot;InvElecHistoryID Missing!&quot;</span>
<span>      Cells(Target.Row, &quot;F&quot;).Select</span>
<span>   End If</span>
<span>End If</span>
<span>End Sub</span>
</code></pre>
<br />
Thanks much,<br />
<br />
gdunn59<br />
<br />
<br />
P.S. &nbsp;Thank you everyone else also for your input and time!
</div>


<div>
You're welcome. Glad your issue resolved ultimately.
</div>


<div>
<div class="content wysiwyg-content">
I have an excel worksheet with 13 columns. &nbsp;I wanted to know how via VBA I could keep the user from entering data in 2 cells, if another specific cell is empty.<br />
<br />

<pre><code class="code-1 nolinks" id="code-10-28928754-1"><span>Empty Column/Cell:  InvElecHistoryID</span>
<span></span>
<span>2 Columns/Cell [DateSent] and [Comments] that data can't be entered into unless the Empty Column above has data.</span>
</code></pre>
<br />
Thanks, <br />
<br />
gdunn59
</div>
</div>


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.

(CODE)

Thanks, 

gdunn59

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

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.