Problem with ranges during the WindowDeactivate event.

[Thingol]

Hi there,

I'm struggling with a problem. I have some code that runs when the window of a workbook deactivates (Workbook_WindowDeactivate). The problem is that I want to use a range in a sheet of the workbook of which the window is being deactivated. The code should test the values of the cells in the range, and depending on the values perform some further actions. The problem that occurs, is that as soon as I want to select a range of cells(1) or I just want to refer to a range(2), error messages pop up:

(1)
ThisWorkbook.Worksheets("Blad1&quot.Range("AA2&quot.Select
'I get an error message that the selection of the object
'class range didn't work.

(2)
dim Range1 as Range
Range1 = ThisWorkbook.Worksheets("Blad1&quot.Range("AA2&quot
'Error 91:
'Objectvariable or blockvariable with is not set.

Why can't I refer to these cells using the .Range property of cells in the workbook that is being deactivated? it should work from within the WindowDeactivate event, shouldn't it? What's going wrong?

 

[SkipVought]

Hi, Thingol,
Maybe I can shed some light on this.

Just ask yourself, how can you "Select" a range in a worksheet that is NOT ACTIVE. The definition of Select implies an active sheet.

But, hang on! All is not lost. You can REFER to a range in a deactivated sheet, but you cannot SELECT a range in a deactivated sheet.

So here's what you might do...

    Dim Range1 As Range, ThisWorkbook As Workbook
    Set ThisWorkbook = ActiveWorkbook  ' or Workbooks("MyName.xls")
    ThisWorkbook.Worksheets("NotBlad1").Activate
    Set Range1 = ThisWorkbook.Worksheets("Blad1").Range("AA2")
    MsgBox "I'm on sheet " & ActiveSheet.Name & " but " & Range1.Value & " is on Blad1!"

where the value is AA2 is "secret". ;-) And notice that both objects are Set.
Hope this helps. Skip,
metzgsk@voughtaircraft.com

 

[Thingol]

Great! Thanks!