Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Tricky Blank CodeName Property & Excel 2002
I've seen newsgroup messages back to 1999 trying to deal
with the problem where the CodeName property returns a blank string unless the VBE gets "invoked" by one means or another. One of the simples solutions I've seen suggested fixing this problem by simply making a reference to the VBProject object as shown below: Sub GetCodeName() ' Uncomment these two lines and .CodeName works ' because the VBProject object gets referenced. 'Dim s As String 's = ActiveWorkbook.VBProject.Name MsgBox "CodeName = " & ActiveSheet.CodeName End Sub However, in Excel 2002 (and higher I assume) any reference to the VBProject object results in the error "Programmatic access to Visual Basic Project is not trusted". You can make this error go away by enabling Tools Macros Security Trusted Souces Trust access to Visual Basic Project. Here's the problem. I must use the CodeName property in my Add-In that needs to work in Excel 2000 and higher. I really do not want to have to require the user to enable "Trust access to Visual Basic Project" in order for my Add-In to work properly on Excel 2002 and higher. Help! Does anyone have a solution for me? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Tricky Blank CodeName Property & Excel 2002
Maybe just looping through the worksheets:
Option Explicit Sub testme() Dim wks As Worksheet Dim myCodeName As String myCodeName = "" For Each wks In ActiveWorkbook.Worksheets If wks.Name = ActiveSheet.Name Then myCodeName = wks.CodeName Exit For End If Next wks If myCodeName = "" Then MsgBox "this shouldn't happen!" Else MsgBox myCodeName & vbNewLine & wks.Name End If End Sub Bob wrote: I've seen newsgroup messages back to 1999 trying to deal with the problem where the CodeName property returns a blank string unless the VBE gets "invoked" by one means or another. One of the simples solutions I've seen suggested fixing this problem by simply making a reference to the VBProject object as shown below: Sub GetCodeName() ' Uncomment these two lines and .CodeName works ' because the VBProject object gets referenced. 'Dim s As String 's = ActiveWorkbook.VBProject.Name MsgBox "CodeName = " & ActiveSheet.CodeName End Sub However, in Excel 2002 (and higher I assume) any reference to the VBProject object results in the error "Programmatic access to Visual Basic Project is not trusted". You can make this error go away by enabling Tools Macros Security Trusted Souces Trust access to Visual Basic Project. Here's the problem. I must use the CodeName property in my Add-In that needs to work in Excel 2000 and higher. I really do not want to have to require the user to enable "Trust access to Visual Basic Project" in order for my Add-In to work properly on Excel 2002 and higher. Help! Does anyone have a solution for me? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Tricky Blank CodeName Property & Excel 2002
Dave,
Thanks for the possible fix, but that doesn't fix the problem in an XLA Add-In. Oddly, the CodeName property always works properly in an XLS, but not in an XLA. I suppose for an XLA the VBE must be brought into the picture, thus causing CodeName to work. Bob -----Original Message----- Maybe just looping through the worksheets: Option Explicit Sub testme() Dim wks As Worksheet Dim myCodeName As String myCodeName = "" For Each wks In ActiveWorkbook.Worksheets If wks.Name = ActiveSheet.Name Then myCodeName = wks.CodeName Exit For End If Next wks If myCodeName = "" Then MsgBox "this shouldn't happen!" Else MsgBox myCodeName & vbNewLine & wks.Name End If End Sub -- Dave Peterson . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Tricky Blank CodeName Property & Excel 2002
Two questions:
1. Are you saying that with the code in an xla, it fails to find the code name in the activeworkbook? 2. Or are you saying that the no matter where the code is, it fails to find the codename of a worksheet in an xla file? If you meant the first, I just tried it in xl2002 and it found the correct worksheet for codename Sheet1 in a .xls file. If you meant the latter, then maybe it's the activeworkbook.worksheets that's causing problems. (I've never seen a .xla file the activeworkbook.) What happened when you tried it and what version of excel are you using? Bob wrote: Dave, Thanks for the possible fix, but that doesn't fix the problem in an XLA Add-In. Oddly, the CodeName property always works properly in an XLS, but not in an XLA. I suppose for an XLA the VBE must be brought into the picture, thus causing CodeName to work. Bob -----Original Message----- Maybe just looping through the worksheets: Option Explicit Sub testme() Dim wks As Worksheet Dim myCodeName As String myCodeName = "" For Each wks In ActiveWorkbook.Worksheets If wks.Name = ActiveSheet.Name Then myCodeName = wks.CodeName Exit For End If Next wks If myCodeName = "" Then MsgBox "this shouldn't happen!" Else MsgBox myCodeName & vbNewLine & wks.Name End If End Sub -- Dave Peterson . -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Tricky Blank CodeName Property & Excel 2002
Hi Bob,
Thank you for posting in MSDN managed newsgroup! For the security issue of Excel 2002, I'd suggest the kb article 317405 will provide some assistance for you. This kb article introduces the security limitation of office XP for the office VBA environment and object model. Please go to: 282830 PRB: Programmatic Access to Office XP VBA Project Is Denied http://support.microsoft.com/?id=282830 Furthermore, you can use the Application.AutomationSecurity property to set the macro security. The kb article 317405 will help you some. Please go to: 317405 OFFXP: How to Implement Application.AutomationSecurity http://support.microsoft.com/?id=317405 Please feel free to let me know if you have any further questions. Does this answer your question? Thank you for using Microsoft NewsGroup! Wei-Dong Xu Microsoft Product Support Services Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Tricky Blank CodeName Property & Excel 2002
Dave,
I am testing with both Excel 2000 and Excel 2002. I have discovered a new twist to this issue. In all my tests I have never seen ActiveWorkbook.ActiveSheet.CodeName return a blank result when the code was in an XLS. However, in an XLA the .CodeName property returns a blank result in some situations and works properly in other situations. The key to whether .CodeName works or not is tied to whether the workbook and its owned worksheets that .CodeName is being used on have ever been "opened" in the VBE (Alt-F11). For example, load an XLA Add-In that displays the CodeName of the currently active worksheet. Create a new workbook, e.g. Book2.xls, and the Add-In will display a blank CodeName for Sheet1. Now press Alt-F11 to open the VBE. Then close the VBE. Now the Add-In will properly display Sheet1 as the CodeName of the sheet. Next, save Book2.xls to disk and then exit from Excel. Now, to prove that once a workbook has been opened in the VBE then CodeName will forever work after that, start Excel again. Use the Add-In on Sheet1 of the new workbook Book1.xls that Excel creates by default, and you will find that CodeName is blank. Now open Book2.xls that was previously saved to disk, and the Add-In will properly show that CodeName for Sheet1 is Sheet1. Click back on Book1 and CodeName doesn't work on that workbook. Click back on Book2.xls and CodeName works properly on all sheets of that workbook. Apparently, opening a workbook in the VBE does something to that workbook that persists to disk and thereafter CodeName in an Add-In works properly on that workbook. I hope Microsoft fixes this. As for me, I've abandoned using CodeName in my Add-In and have come up with an alternative approach where I use the sheet name along with a pseudo "sheet rename event" suggested by Shah Shailesh. Bob -----Original Message----- Two questions: 1. Are you saying that with the code in an xla, it fails to find the code name in the activeworkbook? 2. Or are you saying that the no matter where the code is, it fails to find the codename of a worksheet in an xla file? If you meant the first, I just tried it in xl2002 and it found the correct worksheet for codename Sheet1 in a .xls file. If you meant the latter, then maybe it's the activeworkbook.worksheets that's causing problems. (I've never seen a .xla file the activeworkbook.) What happened when you tried it and what version of excel are you using? Bob wrote: Dave, Thanks for the possible fix, but that doesn't fix the problem in an XLA Add-In. Oddly, the CodeName property always works properly in an XLS, but not in an XLA. I suppose for an XLA the VBE must be brought into the picture, thus causing CodeName to work. Bob -----Original Message----- Maybe just looping through the worksheets: Option Explicit Sub testme() Dim wks As Worksheet Dim myCodeName As String myCodeName = "" For Each wks In ActiveWorkbook.Worksheets If wks.Name = ActiveSheet.Name Then myCodeName = wks.CodeName Exit For End If Next wks If myCodeName = "" Then MsgBox "this shouldn't happen!" Else MsgBox myCodeName & vbNewLine & wks.Name End If End Sub -- Dave Peterson . -- Dave Peterson . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Tricky Blank CodeName Property & Excel 2002
Yep. I see it.
In fact, I think it's been a problem for awhile (since xl97 at least). I used to have a routine that was based on David McRitchie's Table of contents (http://www.mvps.org/dmcritchie/excel/buildtoc.htm) and I wanted to print the codename. But the added worksheet's codename never would appear. (I just dumped the codename--it wasn't important enough to me to keep.) But maybe you could use something like this: In a general module: Option Explicit Private Declare Function LockWindowUpdate Lib "USER32" _ (ByVal hwndLock As Long) As Long Private Declare Function GetDesktopWindow Lib "USER32" () As Long Sub WindowUpdating(Enabled As Boolean) 'Completely Locks the Whole Application Screen Area, 'including dialogs and the mouse. Dim Res As Long If Enabled Then LockWindowUpdate 0 'Unlock screen area Else Res = LockWindowUpdate(GetDesktopWindow) 'Lock at desktop level End If End Sub Sub testme01() With Application.VBE.MainWindow Call WindowUpdating(False) .Visible = True .Visible = False Call WindowUpdating(True) End With MsgBox ActiveWorkbook.ActiveSheet.CodeName End Sub But SAVE your work before you run it (in every open application!). The windowupdating stuff actually freezes the pc. If something bad happens, you'll have to reboot. (application.screenupdating stops excel from flickering, but doesn't have any affect on other applications (like the VBE--kind of another app.)) (If I get industrious, I may add it to my stolen version of David McRitchie's code.) And it seemed to work ok for me under win98 and xl2002. Bob wrote: Dave, I am testing with both Excel 2000 and Excel 2002. I have discovered a new twist to this issue. In all my tests I have never seen ActiveWorkbook.ActiveSheet.CodeName return a blank result when the code was in an XLS. However, in an XLA the .CodeName property returns a blank result in some situations and works properly in other situations. The key to whether .CodeName works or not is tied to whether the workbook and its owned worksheets that .CodeName is being used on have ever been "opened" in the VBE (Alt-F11). For example, load an XLA Add-In that displays the CodeName of the currently active worksheet. Create a new workbook, e.g. Book2.xls, and the Add-In will display a blank CodeName for Sheet1. Now press Alt-F11 to open the VBE. Then close the VBE. Now the Add-In will properly display Sheet1 as the CodeName of the sheet. Next, save Book2.xls to disk and then exit from Excel. Now, to prove that once a workbook has been opened in the VBE then CodeName will forever work after that, start Excel again. Use the Add-In on Sheet1 of the new workbook Book1.xls that Excel creates by default, and you will find that CodeName is blank. Now open Book2.xls that was previously saved to disk, and the Add-In will properly show that CodeName for Sheet1 is Sheet1. Click back on Book1 and CodeName doesn't work on that workbook. Click back on Book2.xls and CodeName works properly on all sheets of that workbook. Apparently, opening a workbook in the VBE does something to that workbook that persists to disk and thereafter CodeName in an Add-In works properly on that workbook. I hope Microsoft fixes this. As for me, I've abandoned using CodeName in my Add-In and have come up with an alternative approach where I use the sheet name along with a pseudo "sheet rename event" suggested by Shah Shailesh. Bob -----Original Message----- Two questions: 1. Are you saying that with the code in an xla, it fails to find the code name in the activeworkbook? 2. Or are you saying that the no matter where the code is, it fails to find the codename of a worksheet in an xla file? If you meant the first, I just tried it in xl2002 and it found the correct worksheet for codename Sheet1 in a .xls file. If you meant the latter, then maybe it's the activeworkbook.worksheets that's causing problems. (I've never seen a .xla file the activeworkbook.) What happened when you tried it and what version of excel are you using? Bob wrote: Dave, Thanks for the possible fix, but that doesn't fix the problem in an XLA Add-In. Oddly, the CodeName property always works properly in an XLS, but not in an XLA. I suppose for an XLA the VBE must be brought into the picture, thus causing CodeName to work. Bob -----Original Message----- Maybe just looping through the worksheets: Option Explicit Sub testme() Dim wks As Worksheet Dim myCodeName As String myCodeName = "" For Each wks In ActiveWorkbook.Worksheets If wks.Name = ActiveSheet.Name Then myCodeName = wks.CodeName Exit For End If Next wks If myCodeName = "" Then MsgBox "this shouldn't happen!" Else MsgBox myCodeName & vbNewLine & wks.Name End If End Sub -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Tricky Blank CodeName Property & Excel 2002
Hi Bob,
With Dave Peterson's suggested method try this code. Assign this macro to a custom button in your toolbar. Close your VBE Editor. Create a New workbook & click your custom button. Works in xl-2000. Sub ShowCodeName() Application.VBE.MainWindow.Visible = False MsgBox ActiveSheet.CodeName End Sub Regards, Shah Shailesh http://members.lycos.co.uk/shahweb/ ---------------------------------------------------- *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Tricky Blank CodeName Property & Excel 2002
Shah,
Thanks for the suggestion, but accessing any property of Application.VBE results in the error "Programmatic access to Visual Basic Project is not trusted" in Excel 2002. I don't want to require users of my Add-In to lower their security simply so that my Add-In works properly. As a result, I've abandoned using CodeName and am using worksheet.Name along with your method of detecting when a worksheet has been renamed. Bob -----Original Message----- Hi Bob, With Dave Peterson's suggested method try this code. Assign this macro to a custom button in your toolbar. Close your VBE Editor. Create a New workbook & click your custom button. Works in xl-2000. Sub ShowCodeName() Application.VBE.MainWindow.Visible = False MsgBox ActiveSheet.CodeName End Sub Regards, Shah Shailesh http://members.lycos.co.uk/shahweb/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Tricky Blank CodeName Property & Excel 2002
Dave,
Thanks for the suggestion, but accessing any property of Application.VBE results in the error "Programmatic access to Visual Basic Project is not trusted" in Excel 2002. I don't want to require users of my Add-In to lower their security simply so that my Add-In works properly. Bob -----Original Message----- Yep. I see it. In fact, I think it's been a problem for awhile (since xl97 at least). I used to have a routine that was based on David McRitchie's Table of contents (http://www.mvps.org/dmcritchie/excel/buildtoc.htm) and I wanted to print the codename. But the added worksheet's codename never would appear. (I just dumped the codename--it wasn't important enough to me to keep.) But maybe you could use something like this: In a general module: Option Explicit Private Declare Function LockWindowUpdate Lib "USER32" _ (ByVal hwndLock As Long) As Long Private Declare Function GetDesktopWindow Lib "USER32" () As Long Sub WindowUpdating(Enabled As Boolean) 'Completely Locks the Whole Application Screen Area, 'including dialogs and the mouse. Dim Res As Long If Enabled Then LockWindowUpdate 0 'Unlock screen area Else Res = LockWindowUpdate(GetDesktopWindow) 'Lock at desktop level End If End Sub Sub testme01() With Application.VBE.MainWindow Call WindowUpdating(False) .Visible = True .Visible = False Call WindowUpdating(True) End With MsgBox ActiveWorkbook.ActiveSheet.CodeName End Sub But SAVE your work before you run it (in every open application!). The windowupdating stuff actually freezes the pc. If something bad happens, you'll have to reboot. (application.screenupdating stops excel from flickering, but doesn't have any affect on other applications (like the VBE--kind of another app.)) (If I get industrious, I may add it to my stolen version of David McRitchie's code.) And it seemed to work ok for me under win98 and xl2002. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: Can I not overwriting non blank destination cells ? | Excel Discussion (Misc queries) | |||
Excel 2002: Return blank when VLOOKUP on blank cells | Excel Discussion (Misc queries) | |||
Excel 2002: How to add blank spaces to text and numbers | Excel Discussion (Misc queries) | |||
Excel 2002 : Problem in moving along blank cells | Excel Discussion (Misc queries) | |||
How can Excel 2002 start with blank workbook? | Excel Discussion (Misc queries) |