Need help with List Validation cells and VBA

[BobBarker]

I have an excel sheet that I'm making for people to use.

On this sheet, there's a Data > Validation > List cell. It get's the list from items elsewhere on the page (hidden).

Now I am trying to create a range of cells that update dynamically depending on what is selected from that list.

For example if the list selection is blank, the dynamic range is blank.
If it is "Apple", I want to fill up the range with other cells for Apple
If it is "orange", I want to fill up that same range with other unique Orange cells.

How can I achieve this? I can't seem to find anywhere how to detect what cell is chosen.

[mdmackillop]

Can you post your workbook? Use Manage Attachments in the Go Advanced reply section.

[BobBarker]

I uploaded a sample. I hope it's clear what I want to do.

Basically the Extended Fields Range from let's say E4:G12 needs to be dynamic and readjust each time the selection of C4 is changed.

[Bob Phillips]

I don't think it is clear.

It is not clear what to do if Apple is selected, if Banana is selected, and so on.

[BobBarker]

Ok a more basic example:

If apple is selected from the drop down, insert "Apple" in cell G1
If orange is selected from the drop down, insert "Orange" in cell G1
If nothing is selected from the drop down (it's blank), clearcontents of cell G1

I want to do this by setting a Change event handler on the list so it updated dynamically, as described above. My problems are that I don't know how to do such a thing in VBA and I don't know how to retrieve the value selected from the list.

[BobBarker]

Sorry I couldn't edit my old post so I doubled posted.

Some pseudocode perhaps (this is how far I've gotten)

[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target is cell C4
If Target.value = "Apple"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Apple"
Else If Target.value = "Orange"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Orange"
Else
Range("G1").Select
Selection.ClearContents
End If
End If
End Sub

[/vba]

[Bob Phillips]

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target

If .Address = "$C$4" Then

If .Value = "Apple" Or .Value = "Orange" Then

Me.Range("G1").Value = .Value
Else

Me.Range("G1").ClearContents
End If
End If
End With
End Sub
[/vba]

[mdmackillop]

At a guess, in F7 enter =VLOOKUP($C$4,$A$21:$H$32,ROW()-5,0) and copy down. Your example is a little sparse.

[BobBarker]

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target

If .Address = "$C$4" Then

If .Value = "Apple" Or .Value = "Orange" Then

Me.Range("G1").Value = .Value
Else

Me.Range("G1").ClearContents
End If
End If
End With
End Sub
[/vba]

This is what I need, but for some reason it doesn't update instantly. I always need to click away and reclick the ListBox for the G1 updating to occur

I think to fix it I need to remove "If Address=C4" and only check for Value = "Apple" @ address = C4 for every cell. But I don't know how to code that.

@mdmackillop I'm sorry about the sparse examples. I really don't know how to explain it better than I have. Regarding your Vlookup, that isn't quite what I'm trying to do I don't think

[BobBarker]

Another double post, sorry!

I got my solution

[vba]
'On Event SelectionChange
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Check value of C4 and test against what we want
If Range("C4") = "Pharmacy" Or Range("C4") = "Orange" Then
'If changes, act on it
Range("G1").Value = Range("C4")
'Otherwise, clean up
Else
Range("G1").ClearContents
End If
End Sub
[/vba]