Accessing/Clearing range in hidden workbook

[DarrenW]

Hello all,

I'm trying to clear the contents and formatting in a hidden workbook
called PSheet, I'm using the command

ThisWorkbook.Sheets("PSheet").Cells.Clear

The only problem is that the command will only work when the Sheet is
unhidden AND active (i.e. the worksheet in the foreground). If the
sheet

The error message given is:

Error 1004

Clear method of range class failed

I have used other commands that use the ThisWorkbook.Sheets format and
they all work OK.

Any ideas would be appreciated.

Thanks

DarrenW

 

[Abdul Salam]

Hello,

Try first toset visible to true and after clearing the
cells visible property to false

ThisWorkbook.Sheets("PSheet").Visible = True
ThisWorkbook.Sheets("PSheet").Cells.Clear
ThisWorkbook.Sheets("PSheet").Visible = false


Abdul Salam

 

[Dave Peterson]

Your code worked ok for me in xl2002.

Are you running xl97?
If yes, are you executing your macro from a control from the controltoolbox
toolbar placed on a worksheet?

If yes, try changing the .takefocusonclick property to false. If the control
doesn't have this property, add this line to the top of your code:

activecell.activate

(If all this is true, then you've found a bug that was fixed in xl2k.)