0

I've looked at many questions with a similar title, but none seem to match my issue.

The snippet of code below is from an initialization routine that I have been running for years with no issue. It only sets variable values and makes no changes to any cells but is called by other routines to set up the Public variables that I need.

Up till now it has always been called when wsPlan, which is the code name for the sheet, was active. I am currently adding functionality to the workbook on another sheet, where a button will be initiating the subroutine. I am not using the button yet, only testing starting with that sheet active. When I call this routine with this other sheet active I get the 1004 error on the Set StatusFilterIndicator line but not on the Set SpecViewIndicator line.

The Cells row and column numbers were originally variables so I changed them to their values to check if that was an issue but no change. I also rebooted just in case, but no go. However, if I activate wsPlan before calling the routine there is no error. Right now, that is my workaround but I don't like to leave it that way.

The only difference between the two lines is the use of Range, because that is two cells. I can think of no reason that should make a difference and this runs perfectly fine when wsPlan is active. What am I missing? 

Public Sub TableInfo

   Public SpecViewIndicator As Range
   Public StatusFilterIndicator As Range
   .
   .
   .
   Set SpecViewIndicator = wsPlan.Cells(1, 22)
   Set StatusFilterIndicator = wsPlan.Range(Cells(1, 25), Cells(2, 25))
Rey Juna
  • 347
  • 2
  • 12

2 Answers2

2

You need to qualify the Cells too. When wsplan is not active your range otherwise applies to two sheets hence the error. 

With wsPlan 
    Set StatusFilterIndicator = .Range(.Cells(1, 25), .Cells(2, 25))
End With
barrowc
  • 10,444
  • 1
  • 40
  • 53
SJR
  • 22,986
  • 6
  • 18
  • 26
  • Sorry on a phone and can’t format code for some reason. – SJR Mar 16 '19 at 02:24
  • 1
    Unless you are coding in the wsPlan private code sheet in which case all range, cells, etc default to the wsPlan worksheet, not the ActiveSheet. –  Mar 16 '19 at 02:31
  • OK, I don't understand but it works and it only works in the `With` structure. – Rey Juna Mar 16 '19 at 02:34
  • @user11198948 No, this in a module not in the sheet. – Rey Juna Mar 16 '19 at 02:35
  • You don’t need With I just think it looks better. If you don’t specify a sheet the active sheet is assumed. – SJR Mar 16 '19 at 02:36
  • I thought I had specified it sufficiently with `wsPlan.Range`. And I tried your suggestion with the `.Cells` and got a compile error when not inside the `With` statement. – Rey Juna Mar 16 '19 at 02:40
2

Both Cells need to explicitly have the same parent worksheet as Range.

'in your code, Cells belong to whatever is the worksheet in the foreground (i.e. the ActiveSheet)
'while Range explicitly belongs to the wsPlan worksheet
'this only works if wsPlan is the ActiveSheet
Set StatusFilterIndicator = wsPlan.Range(Cells(1, 25), Cells(2, 25))

'you need to ensure they all belong to wsPlan
Set StatusFilterIndicator = wsPlan.Range(wsPlan.Cells(1, 25), wsPlan.Cells(2, 25))

'you can shorthand that code by incorporating  With ... End With block
'this is also more efficient as wsPlan is only 'loaded' once
With wsPlan
    Set StatusFilterIndicator = .Range(.Cells(1, 25), .Cells(2, 25))
End With

Note that .Range and both .Cells have a prefix period (.). This is what captures the parent from the With ... End With block. It's like you just took the wsPlan from wsPlan.Range and wsPlan.Cells (leaving the period), and moved it into a With ... End With wrapper.