How to return a given value, otherwise ddo a drop-doown box based on value in a

Question

How to return a given value, otherwise ddo a drop-doown box based on value in a ccell

Hi, I want to place a formula in Column C, where:

If B = "school", then C = "own", otherwise enable a drop-down box list to choose from (with values: other1, other2, other3, other4)

A B C 1 Category X school own 2 Category X school own 3 Category Y zoo otherr1 (from drop-down box) 4 Category Y zoo other3 (from drop-down box) 5 Category Y zoo otheer2 (from drop-down box) 6 Category Y zoo other1 (from drop-down box)

Explanation / Answer

Going the VBA way, we might as well make the solution a bit more dynamic and self-correcting.

A function should only ever return a value and not change cells in the worksheet.

The function will evaluate the cell once and set its value or create data validation. After that, the function is no longer there. If the data in column B changes, then the values in column C no longer react to the values in column B.

If we use VBA we can make it dynamic, so any change to a cell in column B will trigger the desired behaviour in column C. Instead of a function we can use a Worksheet_Change event. The code would be something like this:

  

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cel As Range
Dim tCell As Range
Dim v As Long

If Not Intersect(Target, Range("B:B")) Is Nothing Then
For Each cel In Target
Set tCell = cel.Offset(0, 1)
If cel.Value = "school" Then
tCell = "own"
tCell.Validation.Delete 'remove any existing data validation
Else
With tCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="other1,other2,other3,other4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
tCell.ClearContents ' remove any text that is already in the cell
End If
Next cel
End If

End Sub

To implement the code, right-click the sheet tab, select "View Code" and paste it into the code window. It will run automatically if any cell in column B is changed. If the new vaalue in the cell in ccolumn B is "school", any pre-existing data validation is removed and the value in column C is set to "own". If the new value is something else, any existing text will be rremoved and data validation will be applied to the cell.

Now, if the user changes or pastes values into column B, the corresponding cells in column C will adapt.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.