Hiding a worksheet while formatting it

[Bill Youngman]

I am writing an add-in that takes an Excel workbook that has been exported
from MS Reporting Services and formats it as per client specs when the file
is opened. What I need to be able to do is hide the worksheet and perform
the formatting functions in the background and display the sheet when the
formatting is finished - don't want the user seeing the spreadsheet doing
it's thing right in front of their eyes, they are liable to think that
something is wrong.

I've tried doing

Worksheets(1).Activate

With Worksheets(1)

.Visible = False

(formatting code here)
.Visible = True
End With

Errored out with 'Not able to set Visible property on object'

So I tried inserting a new worksheet making it the visible worksheet and
then formatting the report; but that appears to try and format the new blank
worksheet that I've just created as in

Worksheet.Add

Worksheets(1).Visible = True

Worksheets(2).Activate

With Worksheets(2)
(formatting code)

.Visible = True
End With

.... and then delete worksheet(1).

Any suggestions/ideas are appreciated.

TIA,
Bill Youngman

 

[excelmunkey]

Hi Bill,

Use:
Application.ScreenUpdating = False
When you want the screen to 'freeze', and:
Application.ScreenUpdating = True
to set it back to normal.

Cheers,
JF.

 

[Bill Youngman]

Thanks JF - that worked like a charm