Creating a List Box in an IF statement within MS Excel?

Question

I am running to a snag when i tried to create a conditional statement and a List Box within a single cell. In the column before I have a List Box that will allow me to select between 5 different values. In the following column i set up a conditional statement so that depending on a specific the item selected in the List Box a number displays.

for Instance:

In C1 List Box has A,B,C,D,X

In D1 I have the following conditional statement: =IF(C1="A",7,IF(C1="B",1,IF(C1="C",5,IF(...

I have a value displayed in D1 if A-D is selected, but if X is selected from C1, I would like to have a drop down appear with values 1 through 5 for the user to select. I was able to get this done by just creating a List box in D1 to have values 1 through 5 and have the if statement in the formula bar. However if I select X and select a value from the drop down and then decide to select A instead I no londer have the IF statement to populate the D1 cell.

any ideas on how I can solve this problem.

Thanks

Answer 1

It sounds like you are using macros to manage the list boxes so I will assume you know how to use them.

So why not set your D1 values in the Worksheet_Change subroutine like this.

Private Sub Worksheet_Change(ByVal Target As Range)

lbX.Visible = False

Select Case Range("C1").Text

Case "A": Range("D1") = 7

Case "B": Range("D1") = 1

Case "C": Range("D1") = 5

Case "D": Range("D1") = 3

Case "X": lbX.Visible = True

End Select

End Sub

I have used lbX.visible as a substitute for the code you are using to support the list box functionality and I guessed at the value for Case D. Other than, that this code will ensure that cell C1 always determines what is in cell D1. You won't "lose" the formula.

If the relationship between column C and column D extends on down the worksheet then you would want to change the code to look like:

Private Sub Worksheet_Change(ByVal Target As Range)

lbX.Visible = False

If Target.Column = 3 Then

Select Case Cells(Target.Row, 3).Text

Case "A": Cells(Target.Row, 4) = 7

Case "B": Cells(Target.Row, 4) = 1

Case "C": Cells(Target.Row, 4) = 5

Case "D": Cells(Target.Row, 4) = 3

Case "X": lb.Visible = True

End Select

End If

End Sub

I think that is all you would need to do. Give it a try.