ClearContents and ScreenUpdating issue 1

[BrianWilson]

Hi all,

Hoping you can assist, I'm all out on ideas!

I have a Sub in a module which calls Subs on individual sheets. The Sub in the individual sheets needs to clear a range of cells, but I do not wish the focus to change to any of these sheets from the one the user is viewing. (Note: the Subs are executed via a timer routine).

The issue I am facing is that ClearContents requires the range/sheet to be selected, causing focus to be changed. ScreenUpdating does not help. Here are the code snippets:

Module Code

Sub GetPositions()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Call RETU.SendQuery
    Call FINU.SendQuery
    ' etc.
    ' etc.
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Sheet Code:

Public Sub SendQuery()
    Dim delay As String
    Dim uname As String
    
    Sheets("RETU").Activate
    Range("A7", "L1000").ClearContents
    
    ' code snipped, but essentially ActiveX call
    ' inform user of update:
    RETU.Cells(1, 3) = "Last update : " + CStr(Now())
End Sub

Any ideas?
Regards
BW

 

[HughLerwill]

<<ClearContents requires the range/sheet to be selected>>

I dont't think so.

Have you tried;

Public Sub SendQuery()
Dim delay As String
Dim uname As String

Sheets("RETU").Range("A7", "L1000").ClearContents

' code snipped, but essentially ActiveX call
' inform user of update:
Sheets("RETU").Cells(1, 3) = "Last update : " + CStr(Now())
End Sub

regards Hugh

 

[BrianWilson]

Hugh,
Thanks for the help. When I try this I get:

Run-time error '1004'

ClearContents method of Range class failed

(When I put the Sheets("RETU").Activate code before your suggestion it runs OK - but note that I do not need to do this on the other sheets. However focus is still left on RETU once finished)

To clear up some more...I have a command button on the first sheet which starts timer/poller code in Module1 which in turn calls the GetPositions() Sub above.

Is it the fact that the button is on a different sheet?

Your help appreciated.
BW

 

[HughLerwill]

Brian,

This is working for me given 2 sheets and the following code under a button on sheet 1;

Private Sub CommandButton1_Click()

Sheets(2).Range("A23").ClearContents

End Sub

Excel 2002 on XP Prof both fully SPed.

regards Hugh

 

[BrianWilson]

Hugh,

Thanks for that - I will try this out.

System: Excel 97 SR-2 on NT4 SP2 (work PC!).

Regards
Brian

 

[BrianWilson]

Hugh,

I created a new workbook with your scenario:

Private Sub CommandButton1_Click()

    Sheets(2).Range("A2:D3").ClearContents
    
End Sub

This does not work on my config (see above post).

Whilst it's overkill, is there another way to inderctly code the ClearContents method without using a Range?

Rgds
BW

 

[Zygor]

Have you tried something along the lines of.....
Range("A1:B2").Value =

 

[Zygor]

more specifically
Sheets("Sheet2").Range("A1:B2").Value =

 

[SkipVought]

Hi,

You do NOT have to ACTIVATE or SELECT in order to use the ClearContents method.

    Sheets("RETU").Range("A7", "L1000").ClearContents

Check out this FAQ...

How Can I Make My Code Run Faster? faq707-4105

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[BrianWilson]

Skip,
Thanks for the input, but taking the basic exmaple where on Sheet1 there is a command button with the code

Sheets("RETU").Range("A7", "L1000").ClearContents

..then I still get the Range error. If I precede the code with Sheets("RETU").Activate then it works.

I can only presume this is an Excel 97 issue.

 

[BrianWilson]

Zygor,

Implemented your fix:

Sheets("RETU").Range("A7:L1000").Value = ""

Works a treat and can't see how I missed that workaround!

Many thanks
BW

 

[Zygor]

I'm always receiving help. I'm glad to be able to give some back.