How to uniquely identify worksheet

[Vinit]

Hi friends,

I want to uniquely identify the worksheets in my workbook. I am writing
an add in using VC++. I cant access "CodeName" property of worksheet
using VC++ bcoz it always return blank. The problem in using "Name"
of worksheet is that user can change it any time. There is no event in
excel to keep track of the name change of worksheet. Even I cant use
Worksheets(Index) b'coz sequence can be changed.

Please help me to solve this.

With Best Wishes & Regards,

 

[Guest]

you can refer to the object directly, irrespective of its label
change the name sat from Sheet1 to shMain
in your code

with ShMain
.Range("A1") =.Name
end with

 

[Vinit]

Thanks Patrick,

My problem is in my program I take a cell location from user(e.g.
Sheet1!A1). and inserts value in it using
Worksheets("Sheet1").Range("A1").Value=10.
I again want to insert value in it but now sheet1 is renamed to
Sheet1_old. Now How can I insert the value using the above macro. I
cant use Codename(Changing the sheet name doesn't change the code
name) in VC++

 

[Bob Phillips]

Don't know why codename is blank, but maybe use the worksheet index?

 

[Peter T]

Hi Vint,

I don't know VC++ but am curious as to why attempting to return a sheet's
codename returns blank, or an empty string. In VBA this same problem can
occur trying to return codename of a newly inserted sheet until the workbook
has been saved or resaved (but there are workarounds). However I assume
that's not the scenario in your case.

If you have access to the workbook at an early stage how about inserting a
(hidden) name that refers to a range on your required sheet. Then later you
can return the named range's parent name (ie sheet name). In vba, something
like this:

Sub test()

ActiveWorkbook.Names.Add "MyName", _
Worksheets("Sheet1").Range("A1:z1000")
'ActiveWorkbook.Names("MyName").Visible = False

Worksheets("Sheet1").Name = "Sheet1 Old"

shtName = Range("MyName").Parent.Name
End Sub

If user deletes entire rows or cols of the named range, the last line in the
example will error. Not serious but would need to trap for that and parse
for the sheet name which will still exist. Look for the first "!" and
possibly embraced apostrophes in the string returned with:
s = ActiveWorkbook.Names("MyName")
and remove the initial "="

Regards,
Peter T

 

[Vinit]

Hi Peter,
Thanks!
I find the reason for the blank sheet name. Without opening VBE if we
access CodeName it returns blank. If you open VBE(at this time Excel
initialize the CodeName) and then access the codename it returns
successfuly. It is strange but thats the only reason.

Currently I am using Name Range(i.e."ActiveWorkbook.Names.Add)method to
keep track of Chenge in cell location(insert/Delete) and also for the
worksheet Name. But an interesting problem with it is :

1) Add name range:
ActiveWorkbook.Names.Add "MyName", _
Worksheets("Sheet1").Range("A1")
ActiveWorkbook.Names("MyName").Visible = False

2)Re-Name the Worksheet to Sheet 1 2(include numbers & spaces in
Name)

3)Get the Value of name range:
shtName = Range("MyName").Parent.Name ' Return Value'Sheet 1 2'!A1

4) In this case the sheetname will be 'Sheet 1 2' or in some cases
Sheet'1 2' (Excel adds Single quote(') on its own to sheetname)

5) Application.Range(shtName).value get failed

6)U need to trim the single qupte from ShtName which excel adds if
there is space and numbers in sheetname

Regards,
Vinit

 

[Vinit]

Hi Peter,
Thanks!

Your Approch solved my problem.

I find the reason for the blank sheet name. Without opening VBE if we
access CodeName it returns blank. If you open VBE(at this time Excel
initialize the CodeName) and then access the codename it returns
successfuly. It is strange but thats the only reason.

 

[Peter T]

The blank "codename" is a known issue, but only with newly inserted sheets
since the previous save, and whilst the VBE is closed (AFAIK). I'm surprised
this affects you as presumably you are trying to reference a sheet that has
been saved at least once.

In my example I named a large area. That was deliberate to reduce the
chances of user "removing" the range entirely, and hence the need to parse
the Refersto string for the sheetname if that has occurred. I see you are
now naming a just single cell.

Apostrophes get added if the sheet name has certain characters, such as
space, dash etc. So yes, you need to look for the possibility of these.

I don't follow the purpose of this line:

5) Application.Range(shtName).value 'get failed

Anyway, looks like you have an overall solution.

Regards,
Peter T