dynamic sort

[dunkyn]

Using the SheetCalculate event, I am trying to sort a range of data each time the worksheet recalculates.

This works great, except for when the worksheet is initially opened, as it is linked to another data source, where security prices are dynamically updated.

So...When the worksheet opens, it recalculates, and keeps looping through the sort routine.

Any thoughts on how to accomplish a sort every time a security price updates without looping through code when the worksheet opens?


Many Thanks.

 

[xlbo]

Have the line
Application.enableevents = false
before you import the data (in the workbook_open event)
and Application.enableevents = true as the last line of your workbook_open event

This should stop your loop
HTH Rgds
~Geoff~

 

[rmikesmith]

Geoff,

I'm keen to know whether this works or not. My take is that the workbooks are linked rather than code running to update the "master" workbook. If that's the case, it would seem that manipulating EnableEvents inside Workbook_Open would have no effect. Any idea if updating links occurs before or after Workbook_Open runs? If before, I'm thinking that the SheetCalculate event could inspect a global boolean; if true then run the sort procedure, otherwise exit. The boolean would be set to True inside Workbook_Open, possibly in conjucntion with a Timer delay, if necessary. Thoughts?

Regards,
Mike

 

[xlbo]

Mike - good point - hadn't thought about it like that - I was assuming some kind of query to import the new data.
Updatinglinks appears to occur before the workbook open fires (which is really annoying)
Your idea would probably work but maybe just turning events off before the sort and back on after would do the trick.....???

Other than that yes, you'd need a close switch to reset the variable to the non open state. Variable then gets turned to true on workbook open.
Any ideas when the worksheet calculate fires if it is fired by updating links...before or after workbook open ??

Update links
Wb open
ws calc

or

Update Links
ws calc
wb open

Going on holiday from end of today so I don't really have time to test but I'll kepp my eye on this thread

Rgds
~Geoff~

 

[dunkyn]

Thanks for your interest.

You are correct in that there is not a query in the workbook open event. There is a link to a Telemet pricing server.

The links update before the Worksheet open event is triggered, because the first prompt I receive is whether I want to update the links...before the open event instructions are called.

I understand what you are saying conceptually.

I just don't know the syntax to make it work.

Your assistance is very much appreciated.

 

[rmikesmith]

Geoff, if you're still listening: the SheetCalculate event fires after Workbook_Open (makes sense, which is never a guarantee!). So, the order is

Update links
Wb open
ws calc


Wigmore,

I'll put together the syntax of what I have in mind. Back to you soon.

Regards,
Mike

 

[dunkyn]

Here is the solution, courtesy of Dave Hawley at

http://www.Ozgrid.com

Private Sub Worksheet_Calculate()
On error Resume Next
Application.enableevents=False
Range("a1:h1000&quot.sort Key1=Range("a2&quot, _
Order1:=xlascending,header:=Yes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xl:ToptoBottom
Application.enableEvents=True
On error GoTo 0
End Sub

 

[rmikesmith]

Wigmore,

Here it is. Place the following declaration in a standard code module :

Public AllowSort As Boolean

Create (or add to) a Workbook_Open event handler to look like this:

Private Sub Workbook_Open()
Dim StartTime As Long
  
  StartTime = Timer
  Do
    DoEvents
  Loop Until Timer > StartTime + 5
  AllowSort = True
End Sub

Lastly, make your SheetCalculate event handler look like:

Private Sub Worksheet_Calculate()
  If AllowSort Then
    "Name of your sort procedure here"
  End If
End Sub

You may or may not need the timer delay; experiment with and without.

Let us know how this works out for you.

Regards,
Mike

 

[dunkyn]

Thank you. Clever.

 

[rmikesmith]

Wigmore,

Not quite clever enough! My strategy does not seem to work. Try Dave's solution. It appears the problem is that the sort routine itself causes the SheetCalculate event to fire, resulting in an infinite loop. This is the purpose of the line

Application.EnableEvents = False

in Dave's code. It disables event handling while the sort routine runs. He then re-enables event handling, ensuring that the next Re-Calc gets handled.

Regards,
Mike