Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
From my Addin, referring to activeworkbook's sheets by their codenames
I have a workbook that has a reference to an add-in. I know that the
reference works, because I can call the addin's functions and variables from within my workbook. However, in my addin, I refer to the workbook's worksheets using their codenames (so that if my users change the worksheet name, it doesn't mess up my code). When I run the code, I get an error message: "Object doesn't support this property or method" Here's an example -- ActiveWorkbook has a worksheet with the codename shtDiary Addin code looks like this: strDiaryDate = ActiveWorkbook.shtDiary.Cells(1,1).Value2 if I change it to refer to the collection using the worksheet name it works just fine: strDiaryDate = ActiveWorkbook.Worksheets("Diary").Cells(1, 1).Value2 Why isn't this working? Any help greatly appreciated. Dianne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
From my Addin, referring to activeworkbook's sheets by theircodenames
What happens when you copy that code into a module in the real workbook? It
didn't work for me. The only way I know to use the codename from a different workbook is to cycle through the sheets and check: Option Explicit Sub testme01() Dim wks As Worksheet Dim mySheet1 As Worksheet Set mySheet1 = Nothing For Each wks In ActiveWorkbook.Worksheets If StrComp(wks.CodeName, "sheet1", vbTextCompare) = 0 Then Set mySheet1 = wks Exit For End If Next wks If mySheet1 Is Nothing Then MsgBox "not found" Else MsgBox "Found it and its name is: " & mySheet1.Name End If End Sub Dianne wrote: I have a workbook that has a reference to an add-in. I know that the reference works, because I can call the addin's functions and variables from within my workbook. However, in my addin, I refer to the workbook's worksheets using their codenames (so that if my users change the worksheet name, it doesn't mess up my code). When I run the code, I get an error message: "Object doesn't support this property or method" Here's an example -- ActiveWorkbook has a worksheet with the codename shtDiary Addin code looks like this: strDiaryDate = ActiveWorkbook.shtDiary.Cells(1,1).Value2 if I change it to refer to the collection using the worksheet name it works just fine: strDiaryDate = ActiveWorkbook.Worksheets("Diary").Cells(1, 1).Value2 Why isn't this working? Any help greatly appreciated. Dianne -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
From my Addin, referring to activeworkbook's sheets by their codenames
"Dave Peterson" wrote in message
... What happens when you copy that code into a module in the real workbook? It didn't work for me. The only way I know to use the codename from a different workbook is to cycle through the sheets and check: Thanks Dave. The code worked fine until I moved it to the addin. If I have to, I'll loop through the activeworkbook worksheets, but I'm hoping not to have to do that. -- Dianne |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
From my Addin, referring to activeworkbook's sheets by their codenames
Hi Dianne,
The following function shows how to retrieve a sheet tab name of a worksheet from another workbook based on its CodeName. However, this will not work if run under Excel 2002 with VBProject protection turned on (which is the default setting). If you're going to have to support Excel 2002 or higher, looping is the better option. Function szSheetTabName(ByRef wkbProject As Workbook, _ ByRef szCodeName As String) As String szSheetTabName = wkbProject.VBProject _ .VBComponents(szCodeName).Properties("Name") End Function -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Dianne" wrote in message ... "Dave Peterson" wrote in message ... What happens when you copy that code into a module in the real workbook? It didn't work for me. The only way I know to use the codename from a different workbook is to cycle through the sheets and check: Thanks Dave. The code worked fine until I moved it to the addin. If I have to, I'll loop through the activeworkbook worksheets, but I'm hoping not to have to do that. -- Dianne |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
From my Addin, referring to activeworkbook's sheets by theircodenames
Much nicer. (And I think I've actually stolen that code from either you or
Chip.) Rob Bovey wrote: Hi Dianne, The following function shows how to retrieve a sheet tab name of a worksheet from another workbook based on its CodeName. However, this will not work if run under Excel 2002 with VBProject protection turned on (which is the default setting). If you're going to have to support Excel 2002 or higher, looping is the better option. Function szSheetTabName(ByRef wkbProject As Workbook, _ ByRef szCodeName As String) As String szSheetTabName = wkbProject.VBProject _ .VBComponents(szCodeName).Properties("Name") End Function -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Dianne" wrote in message ... "Dave Peterson" wrote in message ... What happens when you copy that code into a module in the real workbook? It didn't work for me. The only way I know to use the codename from a different workbook is to cycle through the sheets and check: Thanks Dave. The code worked fine until I moved it to the addin. If I have to, I'll loop through the activeworkbook worksheets, but I'm hoping not to have to do that. -- Dianne -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
From my Addin, referring to activeworkbook's sheets by their codenames
Rob,
Thanks for that -- I like it! Before I rewrite all my code, however, a couple quick questions -- This workbook will be used throughout our company on remote worksites -- probably by about 25 - 40 people. By using the worksheets' codenames, I was trying to eliminate the possibility of problems that would arise if one of the users renamed a sheet. However... Is there a lot of overhead involved in calling this function each time I need to refer to a sheet (although my code doesn't do a lot of it)? Or would I be better off telling everyone not to rename the sheets and then in my code I could just use ActiveWorkbook.Worksheets("SheetName")? I've never used the VBProject object or VBComponents collection before. Is this likely to cause any problems when distributed? By that I mean, if a user doesn't have a full installation of Excel 97, would this code still work? Thanks. Dianne "Rob Bovey" wrote in message ... Function szSheetTabName(ByRef wkbProject As Workbook, _ ByRef szCodeName As String) As String szSheetTabName = wkbProject.VBProject _ .VBComponents(szCodeName).Properties("Name") End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loops with sheet codenames | Excel Discussion (Misc queries) | |||
Referring to cells on other sheets using formula | Excel Discussion (Misc queries) | |||
Referring to sheets in formulas | Excel Discussion (Misc queries) | |||
Edit Sheets in Excel Addin | Excel Discussion (Misc queries) | |||
Pivot shart referring to multiple sheets | Charts and Charting in Excel |