Excel 2007 VBA Syntax for Range reference that used to work, doesn't anymore

Question

I hope this isn't something that has been asked before. And I REALLY hope that there is something wrong and I'm not just missing something...

I'm making a macro in Excel 2007 to clear the input ranges in a workbook. I'm using a CommandButton to start it and the procedure is in a module. Using syntax like:

Worksheets("Interview").Range("D6:D17").ClearContents

works just fine. However if I try to select or activate a Range on the same sheet without selecting the sheet first i get:

Error 1004 (Select method of Range class failed)

The following don't work to select the range

Worksheets("Interview").Range("B17").Select (unless its after selecting the sheet)

or

    Worksheets("Interview").Select
    Range("B17").Select


    With Worksheets("Interview")
        .Range("B17").Select
    End With

The only way to get it to work is select the Sheet and then select the range using the full statement. Like this:

    Worksheets("Interview").Select
    Worksheets("Interview").Range("B17").Select

I'm baffled. I hope its not because I'm tired...

I hope I've been clear enough and used the code tags and blocks etc right...

score 1 · Accepted Answer · answered Jun 08 '13 at 01:32

1

I will give you an example & hope it helps.

Consider a house which has 3 rooms (room1 , room2 & room3) in it. Each room has swtichboard for fan & light in it.

Now To on/off the lights of room 1 you have press the buttons in room 1 (you have to be in room 1 for pressing the button).
To on/off the lights of room 2 you have press the buttons in room 2 (you have to be in room 2 for pressing the button).
To on/off the lights of room 3 you have press the buttons in room 3 (you have to be in room 3 for pressing the button).

If you are in room 1 you cannot on/off the lights of room2 or room3.

Same is the case here, when you are in Worksheets("Interview") & its active sheet then only you can select the range.

answered Jun 08 '13 at 01:32

Santosh

12,175
4
41
72

But when you're in the room you don't have to refer to the full address of the lightswitch if you only have one to choose from. – Phrayzur Jun 09 '13 at 06:00
I appreciate the help Santosh. I understand your comparison totally. One of the problems I'm having is that even when I'm in the room with the lightswitch I have to tell it what room to turn off the lightswitch in. Even if I've selected `Worksheets("Interview")` and made it active, I can't just use `Range("B17").Select`. I have to use `Worksheets("Interview").Range("B17").Select`. – Phrayzur Jun 09 '13 at 06:12
Then may be you in different house. Do you have multiple workbook open ? What are you trying to achieve ? Most of the time you dont need Select / Activate . Refer this [link](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) – Santosh Jun 09 '13 at 06:15
I don't have another workbook open, but I did add a window within excel to the same workbook. That shouldn't make a difference should it? – Phrayzur Jun 09 '13 at 06:18
@Phrayzur It should not ? `MsgBox Worksheets("Interview").Parent.Name` will show the workbook name which it refers to. – Santosh Jun 09 '13 at 06:21
I'm actually using poor coding techniques with the select statement, but I'm past that point now. I'm just trying to figure out why I have to use the full address within the sheet. This is now just a learning thing. :) – Phrayzur Jun 09 '13 at 06:22
@Phrayzur Do you have team viewer ? Can i see the issue ? – Santosh Jun 09 '13 at 06:23
It's referring to the correct sheet... Its odd. I wonder if its because of excel crashing on this sheet the other day... – Phrayzur Jun 09 '13 at 06:25

score 0 · Answer 2 · edited Mar 24 '16 at 15:52

0

Application.GoTo Sheets("Interview").Range("b17")

Should work

edited Mar 24 '16 at 15:52

Jon Peltier

5,895
1
27
27

answered Jun 07 '13 at 21:14

exussum

18,275
8
32
65

1

It does! Thanks. But why don't the others work? They were working yesterday. Same sheets, code and formulas. I had the code crash the sheet but I didn't have time to test it yesterday. – Phrayzur Jun 07 '13 at 21:21
Not 100% sure. I was just offering an alternative syntax – exussum Jun 07 '13 at 21:23
And, actually, now they are. What am I missing? – Phrayzur Jun 07 '13 at 21:24
All the examples I gave now work again. Is this some kind of bug? Can anyone enlighten me? – Phrayzur Jun 07 '13 at 21:32
Are you always on the same sheet ? – exussum Jun 07 '13 at 21:33
It starts from the same sheet – Phrayzur Jun 07 '13 at 21:37

score 0 · Answer 3 · answered Jun 28 '16 at 20:45

Ive tested it on excel 2010 and all your code is valid and works. You shouldn't need to select anything first no matter what version of excel you are using. I would suggest prefixing the worksheets("Interview") with thisworkbook eg thisworkbook.worksheets("Interview")

this way you are sure if you have multiple workbooks open that you are trying to select the correct sheet in the correct workbook.

Excel 2007 VBA Syntax for Range reference that used to work, doesn't anymore

3 Answers3