Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations MintJulep on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

need help with changing cell name in worksheet using VBA

Status
Not open for further replies.
TomBarsh

TomBarsh

Structural
Joined
Jun 20, 2002
Messages
1,003
Location
CA
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.
 
Sort by date Sort by votes
Hi,

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

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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).
 
Upvote 0 Downvote
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
7ofakss

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:
 
Upvote 0 Downvote
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Please post the exact code in context, that is a problem.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
I didn't see Sheet2???

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
Upvote 0 Downvote

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


Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dik
  • Locked
  • Question Question
Convert Cell Types
Replies
10
Views
1K
dik
dik
Tuckabag
  • Locked
  • Question Question
Acessing CMD shell in Excel/VBA with keystrokes and copying resultant data
Replies
8
Views
1K
3DDave
3DDave
BhagyeshVashi
  • Locked
  • Question Question
Need a rename macro for excel
Replies
11
Views
2K
sdz
sdz
n8cole
  • Locked
  • Question Question
Looking for an AI that will help me create a chemical functional group database
Replies
7
Views
3K
pierreick
pierreick
LOTE
  • Locked
  • Question Question
Interpolate Help
Replies
7
Views
1K
IDS
IDS

Part and Inventory Search

Sponsor

Back
Top