Populate Cell using VB Code please help!!!!

[slimimi]

Hi there. I need to use this code on a spreadsheet

=MT4|BID!EURUSDm

It uses DDE to talk to a program called MetaTrader and this places 'Live' price quotes in that cell.

Problem
1.User selects currency pair in Cell C4
2.I need code to place a formula in Cell F4 constructed as follows:

=MT4|BID!cell c4m

I have been trying to get this work for weeks now but with no joy
Whatever i try to do - it only places the above as TEXT in that cell, even if i open a new sheet and cells are set to General format, however - when i type the above code in a cell - i have no problems and start getting price data straight away. Hmmmmmmmmm. I just dont get it.

Would really appreciate someones help on this.
Thanks in advance.

 

[slimimi]

(i have re-arranged the sheet so the columns have changed) sorry for confusion.

So if there is no value in Column C - then i would like the contents of Columns D and E to be cleared (if possible).

Would you happen to know how to do this please?

 

[Scott Huish]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Columns(3))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
    If c <> "" Then
        c.Offset(, 1).Formula = "=MT4|BID!" & Replace(c, "/", "") & "m"
        c.Offset(, 2).Formula = "=MT4|ASK!" & Replace(c, "/", "") & "m"
    Else
        c.Offset(, 1).Resize(, 2).ClearContents
    End If
    Next
Application.EnableEvents = True
End Sub

If you want to be able to blank the value in Column C and have it remove the formulas, add this as well:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Columns(3))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        If c = "" Then c.Offset(, 1).Resize(, 2).ClearContents
    Next
Application.EnableEvents = True
End Sub