Opening and closing an InfoBox

Hi Forum,

Nice to be back and Happy New Year. I have a rather simple question that is humbling me:

I have the following function which refreshes a series of Bloomberg data on my sheet, waits 4 seconds for the info to be loaded, and then call a function whose purpose is to transfer the downloaded data onto another sheet.

Sub BBGRefresh()
    
    Application.Run "RefreshEntireWorksheet"
    
     Application.OnTime (Now + TimeValue("00:00:04")), "BBGTransit"

What I want is a way of displaying a textbos that will say: "Please wait while we retrieve your data". This textbox appears Inmediately before the RefreshEntireWorksheet, and should automatically close after the four seconds pause given for the values to update.

I thought that maybe this cannot be done this way and it would be easier to open the text box in the function and then close it in the function BBGTransit. Anyhow, I donot know how to Open and close a text box ( I do not want the "OK" option from msgbox or inputbox)

Thanks in advance

Hi,

you can use an UserForm with its ShowModal property to False …

Userform.Show to open it, Unload Userform to close it …

Could you provide an example please? Ive never used them before...

You have to build the Userform first.

A simplest way : use an empty cell to show the message …

I am not familiar with building userforms. Is there no other way?

As written, use a cell …

I cant use a cell it will look like crap....Could you provide some example code on the userform?

I have now created the userform but using .show only displays it for a split second! How can I get it to stay on until my cells have updated? thanks

Weird ‼ Mine stays in front until I close it …

Depends on your code …

Even in break mode it just flashes and instantly closes...

Sub Form()

StandbyForm.Show

Application.OnTime (Now + TimeValue("00:00:04")), "BYB"

Unload StandbyForm

End Sub


Sub BYB()

End Sub

Sub Form()
    StandbyForm.Show
    Application.Wait Now + TimeValue("0:0:4")
    Unload StandbyForm
    BYB
End Sub

Thanks it works well now but my links do not update during those four seconds...Is there any way thAT I can achieve this? Thanks

Hey dont worry I just solved it - one last question, how can I specify the area of the screen on which the userform pops up? Its showing itself in the bottomright corner for some weird reason/. Thanks

In its Properties window, see StartUpPosition, Left & Top …

And solved it too. Many thanks mate!