INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

need help with changing cell name in worksheet using VBA

need help with changing cell name in worksheet using VBA

(OP)
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.

RE: need help with changing cell name in worksheet using VBA

Hi,

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: need help with changing cell name in worksheet using VBA

(OP)
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.

RE: need help with changing cell name in worksheet using VBA

(OP)
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

RE: need help with changing cell name in worksheet using VBA

(OP)
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).

RE: need help with changing cell name in worksheet using VBA

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: Eng-Tips.com Forum Policies
[IMG http://tinyurl.com/7ofakss]
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


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

RE: need help with changing cell name in worksheet using VBA

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: need help with changing cell name in worksheet using VBA

(OP)
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.

RE: need help with changing cell name in worksheet using VBA

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: need help with changing cell name in worksheet using VBA

(OP)
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.

RE: need help with changing cell name in worksheet using VBA

I didn't see Sheet2???

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: need help with changing cell name in worksheet using VBA


CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: need help with changing cell name in worksheet using VBA

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close