CODEWITHSUNDEEP
excelvbacombobox
Remi
Remi

Reputation: 169

Add IF statement to Combo Box Linked Cell Excel

I have a cell linked to my ComboBox that displays the dropdown value of the ComboBox.

In another cell, I have IF statements that swap out the numbers based on certain scenarios, like : =IF(E38=0,C37,E37)

I tried putting an IF statement where the ComboBox value appears, but, as you guessed, when I run a macro and the ComboBox has to display a new value, the formula is erased.

The Formula I was using was : =IF(E38=0,"ComboBox Value",E37) where ComboBox Value was the name currently in the dropdown. This didn't work. I know it won't work in the LinkedCell property either.

Is there a way to incorporate this using VBA or a formula or conditional formatting or something?

Thanks in advance. Regular Case

enter image description here

Upvotes: 0

Views: 3229

Answers (3)

learnAsWeGo
learnAsWeGo

Reputation: 2282

I think that this small demo will point you in the right direction. When value in comboBox changes it will change value in cell of your choosing. ignore the debug.print statements and probably want to use the change event not the click event.

Private Sub ComboBox1_Click()
    Debug.Print ("CHANGED")
End Sub

Private Sub ComboBox1_Change()
    Debug.Print ("CHANGED2")
    ActiveSheet.Cells(1, 1) = ComboBox1.Value
End Sub

Upvotes: 1

r_hudson
r_hudson

Reputation: 193

  1. First link combox to one cell, say C1
  2. Say, you have to update Range("C2") based on your given conditions.

  3. Sub ComboIf()

    If Range("E38").Value = 0 Then Range("C2").Value = Range("C1") Else: Range("C2").Value = Range("E37") End If

    End Sub

  4. Now first select a value from combo box, which will update cell C1, then run the macro, it will update C2- if 0, then whatever current combo value you have selected else with whatever value is in E37.

Upvotes: 1

Aneta
Aneta

Reputation: 149

Did you try =INDEX("Input range","Cell link") to get your "ComboBox Value"?

Upvotes: 1

Related Questions