need help with changing cell name in worksheet using VBA

[TomBarsh]

Using VBA (and Excel 2007), I have a problem with a line where I want to change the cell referenced by a name.

All I am trying to do is assign an existing name to a new cell. In simplest form:

Sub Red()
Dim WB As Workbook
Dim WS As Worksheet

Set WB = ActiveWorkbook
Set WS = Worksheets("Sheet1")

WS.Range("myCell").Offset(4, 0).Name = "myCell"

End Sub

This version works just fine. I can run it all day long.

The problem is if I change the line

WS.Range("myCell").Offset(4, 0).Name = "myCell"
To
WB.WS.Range("myCell").Offset(4, 0).Name = "myCell"

In the latter case I get the error "Object doesn't support this property or method"

In the original case, it is implicit that the range where the name is to be changed is in the active workbook. But I want the operation to act on another (open) workbook, one that is not active. Thus my use of setting variable WB to "some" workbook. For this sample I even set WB to the active workbook...and I still get the error!

Can someone explain this please? Thank you.

 

[SkipVought]

Hi,

When you Set WS, you must fully qualify to the appropriate workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[TomBarsh]

I should add to my original post, that now I realize that my routine fails when I run it when a different sheet in the active workbook is active. To be clear, the VBA module is in the active workbook. The active workbook has two sheets, Sheet1 and Sheet2. The named range is in Sheet1. The Sub runs fine when Sheet1 is active, but when Sheet2 is active the Sub does not have any effect but then there are no errors either, it steps through (F8) just fine.

Skip, I expect that you mean I should say: Set WS = WB.Worksheets("Sheet1") in lieu of Worksheets("Sheet1"). That doesn't seem to work though.

 

[TomBarsh]

Here is a simpler question. This sub works when the active worksheet is "Sheet1" but doesn't do anything (and no error) when "Sheet2" is active.

Sub Red()
Dim WS As Worksheet

Set WS = ActiveWorkbook.Worksheets("Sheet1")

WS.Range("myCell").Offset(4, 0).Name = "myCell"

End Sub

 

[TomBarsh]

well, okay, something is happening. When the target sheet is not the active sheet then the name "myCell" is being created, but it has workbook scope, not worksheet scope. And my original had only worksheet scope (and that's what I need). Another mystery that comes up is that when the sub is run with another sheet active it seems that the newly-named range on the target sheet is always set to the same place (or set once and never again).

 

[IRstuff]

Isn't the whole point of "ActiveSheet" that Excel operates only on the active sheet? Wouldn't you need to activate "Sheet1" to do something on that sheet?

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com:

http://www.engineering.com/AskForum/aff/32.aspx

 

[SkipVought]

I guess I'm having a hard time understanding what you're doing.

Why do you need the same Named Range on multiple sheets in a sheet scope? What's the purpose?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[TomBarsh]

IRStuff: VBA can be used to effect changes on sheets that are not the active sheet, nor even in the active workbook. There's no need to make the sheet the active sheet first. For example, the macro recorder can be used to record a series of changes made to "another" sheet, which becomes the active sheet when this is done manually in the Excel window. But the VBA routine can then be edited so that the same changes can be made; it's more efficient this way.

Skip: I have a workbook with multiple copies of similar tables. I want to "tag" a particular cell in each table, and this tag will move as data changes are made to the tables. It works just fine with the named ranges as limited to worksheet scope. I want to "move" the tag programmatically.

 

[SkipVought]

Please post the exact code in context, that is a problem.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[TomBarsh]

Apparently I have been barking up the wrong Excel tree. I thought that the "range" object would have a "name" property, but apparently that is not the case. The "name" is an object in the worksheet (in that "Names Manager" dialog). So a command like the line below works just fine. The parameter "RefersToR1C1:=" requires a string (to paste into that "Names Manager" dialog) so I have to build the string up as I want it to be. I can run the command as often as I like and it will keep "moving" the name. I put it in a loop and it did that.

WS.Names.Add Name:="myCell", RefersToR1C1:="=Sheet1!R29C3"


Skip: The exact code in context is in my first post.

 

[SkipVought]

I didn't see Sheet2???

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IDS]

Tom, I don't know if it relates to your exact problem, but if you are working with local names in VBA you should have a look at the link below (including the comments):

http://dailydoseofexcel.com/archives/2015/05/29/global-name-meets-local-global-freaks-out/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

With WS
   .Names.Add Name:="myCell", RefersTo:="='" & .Name & "'!" & .Range("myCell").Offset(4, 0).Address(TRUE,TRUE)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[TomBarsh]

Doug: thanks for the link, that was interesting reading. That 'tips' site is always good (as is your own site!)


I have my routine going with the alternative command to the effect of: WS.Names.Add Name:="myCell", RefersToR1C1:="=Sheet1!R29C3"

It's a bit mindboggling to me that such a command as: WS.Range("myCell").Offset(4, 0).Name = "myCell" works but doesn't seem to be able to be generalized.