Is there a way of diplaying a message while an excel spreadsheet is opening?

Posted by Andrew on August 23, 2001 9:42 PM

I have an excel spreadsheet that takes +- 2 minutes to fully open as a result of updating thousands of links. Is there a way to display a message while it is opening so that the users do not think it has crashed?

I tried this code but it only displays once the spreadsheet has opened fully.

Private Sub Workbook_open()
MsgBox "Message", vbOKOnly + vbInformation, "Message Header"
End Sub

Posted by anon on August 23, 2001 9:58 PM

? - 28060.html

Posted by Andrew on August 23, 2001 10:20 PM

That doesn't work

That was the original code that I was given, but the message box only displays when the Excel file has opened fully.

Posted by Damon Ostrander on August 23, 2001 10:58 PM

Re: That doesn't work

Andrew,

Here's an idea. Create a TextBox (not a TextBox control, but a TextBox drawing object--actually a Shape object) on the sheet that first displays so that it appears immediately. The text in the textbox would say something like:

Please wait while links are updated...

Then add code to the Worksheet's Open event that makes the Textbox disappear using the textbox's Visible property, and to the worksheet's Close event that makes it reappear so that it will be visible next time the workbook is first opened:

Shapes("TextBox 1").Visible = False

In place of "TextBox 1" string, insert the actual name of your textbox. This name displays in the Name Box on the left side of the formula bar when you select the TextBox. Also, in the Close event activate the worksheet it is on so that it will be the sheet that first appears when the workbook opens:

Worksheets("My Data").Activate

Happy computing.

Damon

Posted by Andrew on August 24, 2001 3:42 AM

Nope. Only displays once the file has opened

I am looking for a comment to be displayed as soon as you double click on the Excel file to open it,

Posted by Jerid on August 24, 2001 6:24 AM

Re: Nope. Only displays once the file has opened

Andrew, I really think the only way you are going to achieve what you want is if you create a separate Workbook that is used to:
1) Display a "Please Wait" Message
2) Open your Workbook
3) Display a "Thank You for Waiting" Message
4) Close itself

You could do this a few different ways, add a command button and let the user click it, or put the following code into the Workbook_Open event and it will execute automatically.

Code Example

Sub cmdOpen_Click()
Dim sThisWkBook As String

sThisWkBook = ActiveWorkbook.Name

MsgBox "Please wait while Links are updated"
Application.Cursor = xlWait
Workbooks.Open ("C:\Full Path and File Name to your Workbook.xls")
Application.Cursor = xlDefault
MsgBox "Thank You for Your Patience"

Workbooks(sThisWkBook).Close False

End Sub

Hope this help.

Jerid