Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Accessing/Clearing range in hidden workbook

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default Re : Accessing/Clearing range in hidden workbook

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

-----Original Message-----
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
.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default Accessing/Clearing range in hidden workbook

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.)



DarrenW wrote:

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


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting/Clearing duplicate range of cells Steve Excel Worksheet Functions 0 April 28th 10 03:59 PM
Clearing cells without clearing formulas marsjune68 Excel Discussion (Misc queries) 2 April 10th 09 07:39 PM
ACCESSING A SHARED EXCEL 2003 WORKBOOK ISSUES WITH OFFICE 2007 ahbaffledbyoffice Excel Discussion (Misc queries) 0 December 9th 08 04:07 PM
Using a Wildcard (*) in formula accessing sheets in workbook. pwk Excel Worksheet Functions 1 February 4th 07 07:47 PM
Prevent Username Accessing Workbook Martin Whitehead Setting up and Configuration of Excel 1 April 2nd 06 12:44 PM


All times are GMT +1. The time now is 10:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"