Automatically blank a worksheet on startup

[phg]

I have a spreadsheet which has a macro that runs on workbook open. It does a web query to update data. What I want to do is blank the screen (I change the text to white on white) while the query is running so the user is not confused by the old data and then change the updated text back to normal colours after the query is complete.

However on startup the spreadsheet displays normally for a second or two before it is blanked.

Is there any way to run the macro before anyting is displayed?

 

[Jonmo1]

Try clearing the contents of the page before refreshing the query

sheets("sheet1").Cells.ClearContents

now your query code

 

[phg]

I want to keep the data there in case the query fails. I then display tohe old data with a warning.

Also the ClearContents still takes a second or two to clear the screen

 

[Jonmo1]

How about having a different sheet selected, then turn screenupdating off then refresh the query...

sheets("sheet2").Select
Application.Screenupdating = false

Query code here

sheets("querysheet").Select
Application.Screenupdating = true

 

[phg]

Starting from a different sheet works except I would like to allow the user to save the workbook (so they have the most recent data in case the query fails). Then when they open the workbook next they will be starting from the data sheet or wherever they were when they saved.

I tried using a BeforeClose event to blank the data but the blanking occurs before the prompt to save but it is disconcerting since it looks to the user like they are saving a blank sheet.

 

[phg]

I have finally come up with a solution that almost works for me.

I have added a BeforecCose event to the worksheet. The first step is to minimize the window and then do the blanking so the user does not see the spreadsheet being set up. Then when the user opens the spreadsheet the next time the display starts already blanked.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
         Workbooks("test.xls").Windows(1).WindowState = xlMinimized
         Workbooks("test.xls").Activate
         Position_Default(True) 'routine which blanks the display and
                                ' puts the minimized window where you want it
End Sub

My problem now is that if the user cancels the close the spreadsheet has the display blanked and the window minimized. Any suggestions as to how the program can recognize that the close was canceled so I can restore the window and unblank the display?