EXCEL - QUESTION EXPERT (TAB KEY)?
Hi everyone,
I have a spreadsheet and so far I have about 4 columns. All cells are protected except the 4 columns. The 3rd columns has a drop down box on each cell. What I was trying to do is if I or someone else is trying to do an entry that they can't miss the 3rd column by using the tab key. The 3rd column is required an entry. So I want myself or other to remind that the 3rd column need or require an entry. Is this possible, if it is, please tell me by step what I need to do.
Thank you in advance :o)
More Questions From This User See All
Answers & Comments
Verified answer
Here is one way to force an entry in the 3rd column if any entry is made in columns 1, 2, or 4. The following example assumes that your columns are A, B, C, and D.
Copy the following event handler to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy'):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
For i = 1 To Cells.SpecialCells _
(xlCellTypeLastCell).Row
If Application.CountA(Range("A" & i & ":" & "D" & i)) > 0 _
And Cells(i, 3).Value = "" Then
MsgBox "Please make a selection in cell " & Cells(i, 3). _
Address(0, 0), vbExclamation, "Required Field"
Cells(i, 3).Select
Application.EnableEvents = True
Exit Sub
End If
Next
errhandler:
Application.EnableEvents = True
End Sub
Select the worksheet containing the columns to monitor and right click the sheet tab at the bottom.
Select 'View Code'.
Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').
Close the VBE (red button w/white 'x' - top right).
Make an entry in any row in columns 1, 2, or 4, and press ENTER, TAB, or select any cell with the mouse. A message box will display advising that an entry is required in column 3. Until and entry is made in the column, the message box will display with every cell change and take the focus to the cell requiring an entry.
I'm not sure you can stop tab from working if a cell is left empty without going into VBA territory.
What I'd suggest is using conditional formatting to make it so the cell is filled with red if it is empty. You could add a message at the top of the file "Fill out all red cells" or something to that effect.
Hope this helps!