VBA help (again)

[grantx]

DB,

I found the location - compare the attachment to the last. The present, accessed from the sheet via VB Editor, was not the same as that when accessed from the original module.

Your first solution didn’t seem to produce anything (nothing on Status bar). For whatever reason, and I’m assuming this is relevant, “Compile VBA project” wasn’t accessible (greyed out).

I tried the second solution. I was able to “Compile” and the data was showing on the Status bar. However, nothing is appearing in the columns (Bcv and Acv in attachment).

At least we know we have the required data coming in.

Grant.

 

[grantx]

DB,

Any comment on the above re direction?

Grant.

 

[A Dashing Blade]

Apols Grant

Right

The good news is that we now know that changes to N1 or P1 can be captured in the Worksheet_Change event code.

So all you need to do is change the "Application.Statusbar = Target.Value" line (we can, of course, insert as much/little code as we want) to do what you really want to do with these changes. Essentially what's happening is that we can now use the variable "Target" in place of the ExcelDocs(1).Range("P1") stuff we were using previously.

To work out whether Target is N1 or P1 we can use the

If Target.Address = "N1" then

ElseIf Target.Address = "P1" then

End If
(instead of the Appl.statusbar line) structure to further refine the process


Define this as fully as possible and I'll talk you through it.

 

[grantx]

DB,

Please refer to the attachment re code (is it correct?).

I've also included shot of sheet for clarification if needed.

Grant

 

[A Dashing Blade]

Just tested

actually, because the default for the .Address property is the fixed style (ie "$N$1" rather then "N1") tou'll have to use either the syntax

If Target.Address(False, False) = "N1" Then

Elseif Target.Address(False, False) = "P1" Then

End IF

OR

If Target.Address = "$N$1" Then

Elseif Target.Address = "$P$1" Then

End IF

the choice is yours. The (False,False) parameters are obviously saying "don't give me the address property in absolute row and absolute column terms.

Note that, in the above example, your comparing two two sets of text ie the .Address property of a range (which is text) and the text "N1". What you & I call text is what IT bods call a string.

 

[grantx]

DB,

Please see attachment, as per instructions. Shouldn't we be specifying a target cell/range?

Grant.

 

[A Dashing Blade]

Er . .. yes

eg

If Target.Address = "$NS1" then
do something eg Me.Range("A1")=Target.Value will copy N1's value into A1
Else
do something else
End if

 

[grantx]

DB,

It's a fair cop. Ignorance may be a defence but stupidity doesn't stand up.

Still isn't working, I'm afraid.

I don't like to burden you with all my queries so I spread them across various sites. And there is a recurring feature of most solutions - they don't work. I don't believe this is due to faults in the codes but a possible conflict with VBA code/modules from my data vendor. This is just a guess - based on either ignorance or stupidity.

For example, the the data in Excel sheet I've attached on a number of occasions has its origins from a VBA charting module here (please refer to the attachment).

The bottom chart is the focus (albeit blank). Once attached to the price (tick) chart data is downloaded into Excel. The code been expanded to include more fields, for example.

What do you reckon?

Grant.

 

[grantx]

Attachment

 

[A Dashing Blade]

So . . . to recap.

1) You get live data into your sheet when you bring in a chart.
2) We've established that when you used the Worksheet_Change event, you DID
see data changing in the status bar

As I understand it, your problem is that altho we can see data in the status bar, you can't get in onto a spreadsheet? Is this correct?

Can you describe what happens (or doesn't) ie any error messages etc?

 

[JOSEBA]

Hello Grantx.

Look for something like Dim BidSize or Dim AskSize. Under this copy and paste into your code
Dim Counterbid As Double
Dim CounterAsk As Double
Under the main block of code shown above copy and paste into your code
Counterbid = BidSize
CounterAsk = AskSize
For i = 2 To 10
If (.LimitVol(i, osBuy, 0, Data) <> NullValue) Then
Counterbid = Counterbid + .LimitVol(i, osBuy, 0, Data)
End If
If (.LimitVol(i, osSell, 0, Data) <> NullValue) Then
CounterAsk = CounterAsk + .LimitVol(i, osSell, 0, Data)
End If
Next i
ExcelDoc.Sheets(1).Cells(2, 19).Value = Counterbid
ExcelDoc.Sheets(1).Cells(2, 22).Value = CounterAsk

I hope this helps you.

Mikel.

 

[Profitaker]

Forgot to mention this last time I swung past:-

http://www.excelforum.com/

 

[grantx]

Mikel,

Excellent. It works. Now I can clean up on Eurex. Watch out Flipper.

PT,

Thanks for the link. I actually started using this site last week (1 question) but it is a really good site.

Grant.

 

[jsteve1999]

Thaks

Thank you to all who put in a lot of hard work on my question I am sorry I could not reply
sooner as I have been slightly ill once again many thanks to all
jsteve1999