Workbook_Open Not Working In ThisWorkbook

[cicconmr]

Let me know if I'm doing this correctly.

I'm double clicking the ThisWorkbook under Excel Object and adding in:

Private Sub Workbook_Open()

Call Sort

End Sub

That's it....can it have something to do with the way I saved my program? This should be working....right?

 

[CharlesChuckieCharles]

<CODE>The wait function</CODE>


</PRE>

<CODE>this example waits 10 secs</CODE>


</PRE>

<CODE>

</CODE>
<CODE>[COLOR=#000066]If Application.[B]Wait[/B](Now + TimeValue("0:00:10")) Then [/COLOR]</CODE>
<CODE>[COLOR=#000066]MsgBox "Time expired"[/COLOR]</CODE>
<CODE>[COLOR=#000066]End If[/COLOR]</CODE><CODE>

</CODE>
<CODE></CODE>
<CODE>or try a more system friendly</CODE>
<CODE>

</CODE>
<CODE>for x = 1 to 25000</CODE>
<CODE>doevents</CODE>
<CODE>next x</CODE>
<CODE>

</CODE>

 

[CharlesChuckieCharles]

in fact don't use WAIT with Bloomberg use DoEvents

 

[cicconmr]

The Bloomberg Links are not refreshing while the For loop is running...I know this isn't a BBerg thread neccesarily, it appears that the links don't work at all while a Macro is Running. Going to try the Wait function though I'm not sure I understand the If statement involved.

 

[cicconmr]

Yeah no dice....the links don't work at all until the macro is done running, I think there was a poster who alluded to this.

This is an interesting thing to consider....So I know there is another way to do things upon start up...what is that way again? And why is it inferior?

 

[CharlesChuckieCharles]

you may want to put

ActiveWorkbook.RefreshAll

but the doevents loop is better than Wait

 

[Andrew Poulsom]

Use the OnTime method to call your macro later.

 

[CharlesChuckieCharles]

You may also want to move it to the workbook_calculate

I use the following code to ensure all my BLP entries have refresh before attempting to use them

cleanct = 0
'check to see if all the blp fields have been updated
For x = 2 To rowct
 For y = 1 To 9
    If Left(Cells(x, y), 4) = "#N/A" Then
        'exit sub if some haven't been updated, an update will trigger a Calculate Event
        cleanct = 0
 '       Application.StatusBar = "RE- checking for #N/A"
        Exit Sub
    Else
        cleanct = cleanct + 1
        DoEvents
    End If
  Next y
 
 Next x

You sort call here may be put a Public Boolean to stop it sorting on every Calculate
if bDoneSort=false then
call Organ.....
bDoneSort=True
endif




Obviously if you do other things to the spreadsheet then the Calculate may cause the sort to be triggered unexpectantly

 

[cicconmr]

The Issue is the DoEvent method is not allowing the BLP to update. It stay's as "N/A Refreshing Data" until the Macro is finished then all the links update.

I'll this code however, in the mean time what is the "OnTime" method and how does that work?

 

[CharlesChuckieCharles]

ActiveWorkbook.RefreshAll

The bit that allows BLP room

 

[cicconmr]

It's almost like the only way I know this will work is if I can put a button in for the user to update, but I really don't want to do that....Bloomberg is realy frustrating.