How to get a cell to display a default value

Question

Hello,

I am trying to create a spreadsheet where a user must select Yes/No in a column (column G), but if the user doesn't select an answer - the cell would default to "No Answer". Also, if the user selects an answer, but then changes his mind and deletes the answer - the cell should default to "No Answer". In other words, in column G, every cell at all times should have either "No Answer" or "Yes" or "No" - there should be no blank cells at any point.

I tried using a formula in cells like =IF(G3="", "No Answer", G3) , but that's easy to delete, leaving the cell blank. What other options are out there?

Answer 1

Data Validation will get you part way but you will have to add VBA to make sure the cells always have an answer.

Select all the cells of concern in column G and choose Data, (Data Tools) Data Validation and set up the following:

I give you some code in a while.

Answer 2

Add this code to the appropriate code sheet:

I have named the range in column G which need answers "Answer"

To get to the VBA area press Alt+F11, find your workbook on the Project Explorer window on the left and double click the Sheet you are working with. In top dropdown on the left choose Worksheet and on the right choose Change. Add the 3 lines of code as shown above.

Answer 3

Thank you, Shane. I applied the coding and the data validation, but I'm still able to hit the delete button and clear the cells. Was I supposed to hit save somewhere after entering the lines of code? - seems like there was a step missing b/c I just entered the code and then clicked out of the window (didn't see a "save" or equivalent button)

Answer 4

You need to name the range where these answer are going to be located. I named that range "Answer". The name of the range must match the entry in the code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("**Answer**")) Is Nothing Then 

ElseIf Target = "" Then Target = "No Answer" 

End If 

End Sub

You can name the range anything you want but it then you must enter that name in place of the bolded entry in the code above.

You name a range by selecting the range in the spreadsheet and then clicking on the Name box and typing the name into the box and pressing Enter.

In this figure I have named the range A1:A10 Answer.

If you already have a name Answer, but referencing a different location than where you want it, choose Formulas, Name Manager select the name and Delete it. Then add your new one as I described above.

Answer 5

Shane, it worked. This is great - thank you!