Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to uniquely identify worksheet
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to uniquely identify worksheet
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" wrote: 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to uniquely identify worksheet
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++ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to uniquely identify worksheet
Don't know why codename is blank, but maybe use the worksheet index?
-- HTH Bob Phillips "Vinit" wrote in message oups.com... 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++ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to uniquely identify worksheet
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" wrote in message oups.com... 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++ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to uniquely identify worksheet
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to uniquely identify worksheet
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to uniquely identify worksheet
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 "Vinit" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Uniquely number a set of names | Excel Worksheet Functions | |||
Uniquely Identify Data for Charting with a List Box | Charts and Charting in Excel | |||
Uniquely Identify Data for Charting with a List Box | Charts and Charting in Excel | |||
Protect Individual Tabs Uniquely | Excel Worksheet Functions | |||
Repost: How do you identify a worksheet as last? | Excel Programming |