flickering cursor

[robcpettit]

Hi, Im logging current prices to a10 on a worksheet. On this worksheet I have a command button to fire a back bet. The problem is, because Im using worksheet change the cursor flickers alot, making using the command button awkward. Ive opened a second instance of excel and put the Button in that. This were I get lost. In A1 l(on the second workbook) Ive put '='I:\MyDocuments\Betting\[tradecharts.xls.sheet1]C'!$B$3', which links to tradecharts sheet B3 and shows the same data. The problem is, it doesnt update when the data changes. Any ideas. Also I cant work out how, with the commandbutton on second workbook, when clicked to have a range on workbook1 to = a certain value. Any ideas please.
Regards Robert

[Mitch]

Do you have calculation set to automatic? Tools, Options, Calculation.

The only other thing I've found occasionally is if you scroll down so the cell in question isn't on the screen then scroll back up again it has sometimes changed, don't know why that happens.

As for the second part, it sounds like it should be easy to do but can you be more specific?

[robcpettit]

Hi Mitch, thanks for replying. Opened excel, with workbook. Logging current prices into worksheet starting at A10. Im using worksheet_change to do some calculations, which results are returned by changing certain cells either red or blue. My idea is, when certain criteria met to back/lay, then offsetting the lay/back part by one tick. I could let excel trigger the bet. But I want to test this, so Ive added a command button beside each horse, which when clicked will change the N cell to back or lay. (Havnt worked that bit out yet). Because Im refreshing the data every 0.5 to 1 sec, the cursor flickers, this is a problem because Ive noticed when I tested the button with just backing, Ive had to click it 2-3 times to catch in betweent the refresh. Am i right in thinking that only one bet would get entered anyway because of the ref number. Anyway I got to thinking that if I put the command button in a second workbook this would solve the problem. It doesnt work in a second workbook in the same instance of excel. It does work though, if I open a completly new excel. My problem is sending instructions from one workbook to the other. If I can send instructions from the command button in another workbook, this will be better alround, because I can do the calcs for the offsetting in the new workbook as well. Hope Ive explained ok.
Regards Robert

[GeorgeUK]

You may be able to adjust your code for this.

What is the worksheet_change event looking at?
All the cells in 65536 rows, 255 columns?

Or have you narrowed it down to just certain cells/ranges.

Hard to really answer this one without seeing the code. Are you turning off and on screenupdating?

[Mitch]

You should be able to have the commandbutton in another sheet in the same workbook.

If you have the code now as Range(N5) = "BACK"
then the code should read Sheet1.Range(N5) = "BACK"

That is assuming the sheet you have BA sending to is Sheet1.


My screen flickers too. I tried my routine with a 0.5sec refresh rate for the first time at the weekend and it gave me a headache concentrating on it because of the flicker. It's not just the cursor - I could live with that - it's the whole sheet.

[Ian]

I use the With statement to control one sheet from another

eg.

With Sheets("Sheet1")

.Range("N5") = "BACK"
.Range("M5") = 10

End With


You keep the same statements you already have but add a dot in front.

[robcpettit]

Mitch, Ive tried the control on the second sheet, still flickers, seems no war around the flickering.
Ian, when you use the With sheets--- does using .Range ensure its related to that sheet.
Regards Robert

[Ian]

Yes anything between the With and End With statement would only apply to the sheet you have indicated.

Its just a shortcut/neater way of doing ....

Sheet1.Range("N5") = "BACK"
Sheet1.Range("N6") = 10

I use it all the time even if I don't really need to. It makes it easier to move code around without suddenly having it action the wrong sheet.

[robcpettit]

Thanks Ian, I hadnt realised that. Ive often seen the .Range but never realised the significance.
Regards Robert