Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Editor Access
G'day there One & All,
At the moment my work is using Windows 2003, although a few forgotten boxes still have XP on them. Office 2003 is universal but I'm not sure what version, nor which service packs are installed for the OS. Regardless of all that, I have a question which may well be stupid, but which I need to ask anyway - stupidity being an attribute which has never stopped me in the past! My employer is a statewide government agency with over 16000 employees. For use of frontline staff it maintains a "forms package" of over 800 standard forms. The vast majority are Word documents, followed by some Excel worksheets, then a few .PDFs and also some URL's to other agency's online forms. By sheer chance I recently found 2 of these Excel worksheets were password protected, but with unsecured VB Projects with users able to view all the code including the "Protect" & "Unprotect" routines with the forms' passwords exposed. I advised the IT section and that was easily fixed. The idea of opening each document and manually checking protection for 800+ docs is not inviting. The IT section's resources are limited and as a bit of an interesting project I commenced writing an Excel application to check both doocument & project protection and store the results in a Worksheet list indicating which were protected and which weren't. My code iterates across the single folder containing all these documents and load worksheets or starts the MS Word application programatically when required. However, I get an error similar to "Program access to VB Editor refused" (I'm using a different computer to write this and don't recall exact wording). This access needs to be set for each document as it's turned off by default. I understand the reasons this is so and appreciate the security issues surrounding such access. Now to the stupid question part: As I only want to check whether the project is protected, is it possible to workaround this access limitation without setting it by hand for each document/workbook? Yes, I re iterate that it's a stupid question. However just because I shouldn't be able to do this doesn't mean that I can't. In this instance I only need to read data, not alter code but I can't really explain that to the application to make it feel happier about me poking about. I think I know the answer already, but thought that more experienced minds than mine might just know otherwise. Thank you See ya Ken McLennan Qld Australia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Editor Access
Hi Ken
The "Trust access to the VBA object model" setting is stored in a registry key, for Excel 14 this is HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\E xcel\Security\AccessVBOM and registry keys can be changed by VBA code. But this is an application setting, not a document setting, so it may not be what you ask for. Beste wishes Harald "Ken McLennan" skrev i melding . au... G'day there One & All, At the moment my work is using Windows 2003, although a few forgotten boxes still have XP on them. Office 2003 is universal but I'm not sure what version, nor which service packs are installed for the OS. Regardless of all that, I have a question which may well be stupid, but which I need to ask anyway - stupidity being an attribute which has never stopped me in the past! My employer is a statewide government agency with over 16000 employees. For use of frontline staff it maintains a "forms package" of over 800 standard forms. The vast majority are Word documents, followed by some Excel worksheets, then a few .PDFs and also some URL's to other agency's online forms. By sheer chance I recently found 2 of these Excel worksheets were password protected, but with unsecured VB Projects with users able to view all the code including the "Protect" & "Unprotect" routines with the forms' passwords exposed. I advised the IT section and that was easily fixed. The idea of opening each document and manually checking protection for 800+ docs is not inviting. The IT section's resources are limited and as a bit of an interesting project I commenced writing an Excel application to check both doocument & project protection and store the results in a Worksheet list indicating which were protected and which weren't. My code iterates across the single folder containing all these documents and load worksheets or starts the MS Word application programatically when required. However, I get an error similar to "Program access to VB Editor refused" (I'm using a different computer to write this and don't recall exact wording). This access needs to be set for each document as it's turned off by default. I understand the reasons this is so and appreciate the security issues surrounding such access. Now to the stupid question part: As I only want to check whether the project is protected, is it possible to workaround this access limitation without setting it by hand for each document/workbook? Yes, I re iterate that it's a stupid question. However just because I shouldn't be able to do this doesn't mean that I can't. In this instance I only need to read data, not alter code but I can't really explain that to the application to make it feel happier about me poking about. I think I know the answer already, but thought that more experienced minds than mine might just know otherwise. Thank you See ya Ken McLennan Qld Australia |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Editor Access
G'day Ken
I use the following to check & kill any codes or modules in a workbook. Now, I know you don't want to kill the coding, but you maybe able to alter it to suit your purposes. I have no idea how it works as I found it online, but it does what I need it to do. HTH Mick. Melbourne. Sub KillVBCode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim StartLine As Long Dim NumLines As Long Dim ProcName As String Dim WillRobinson As Integer WillRobinson = MsgBox("[ DANGER WILL ROBINSON ] YOU ARE ABOUT TO DELETE ALL VITAL CODES FROM THIS FILE, YA REALLY WANNA DO THAT..????", vbYesNo) If WillRobinson = vbYes Then With Application.VBE If Not .ActiveCodePane Is Nothing Then Set .ActiveVBProject = ..ActiveCodePane.CodeModule.Parent.Collection.Pare nt End If End With Call StopTimer Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("Module1") VBProj.VBComponents.Remove VBComp Set VBComp = VBProj.VBComponents("Module2") VBProj.VBComponents.Remove VBComp Set VBComp = VBProj.VBComponents("Module3") VBProj.VBComponents.Remove VBComp Set VBComp = VBProj.VBComponents("Module4") VBProj.VBComponents.Remove VBComp Set VBComp = VBProj.VBComponents("Module5") VBProj.VBComponents.Remove VBComp Set VBComp = VBProj.VBComponents("Module6") VBProj.VBComponents.Remove VBComp Rows("1:3").Select Selection.Delete Shift:=xlUp For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp Else Cancel = True End If Range("A1").Select End Sub Function IsEditorInSync() As Boolean '================================================= ====================== ' IsEditorInSync ' This tests if the VBProject selected in the Project window, and ' therefore the ActiveVBProject is the same as the VBProject associated ' with the ActiveCodePane. If these two VBProjects are the same, ' the editor is in sync and the result is True. If these are not the ' same project, the editor is out of sync and the result is True. '================================================= ====================== With Application.VBE IsEditorInSync = .ActiveVBProject Is _ .ActiveCodePane.CodeModule.Parent.Collection.Paren t If IsEditorInSync = True Then Debug.Print End If End With End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Editor Access
G'day there Harald,
The "Trust access to the VBA object model" setting is stored in a registry key, for Excel 14 this is HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\E xcel\Security \AccessVBOM Thanks for that mate. Not sure how I'll go, the IT people might be persuaded to give me access to the registry on a single machine just to run a check. I'll have to ask them to see. Another option might be to copy the current docs to my home 'pooter and check them all there. Dunno, I'll have to see how I go :) Thanks again, Ken |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Editor Access
G'day there Mick,
I use the following to check & kill any codes or modules in a workbook. Now, I know you don't want to kill the coding, but you maybe able to alter it to suit your purposes. I have no idea how it works as I found it online, but it does what I need it to do. Thanks for that mate. I've gotta head off in a couple of minutes so it will have to wait for me to finish "stuff" before I can play with it :( Looks promising though, but whether my coding skills are up to the task might be a different question!!! Thanks again Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programatic access to VB-editor not trusted | Excel Programming | |||
How to access Visual Basic Editor via VBA? | Excel Programming | |||
Unable to access VBA editor | Excel Discussion (Misc queries) | |||
VB Editor | Excel Programming | |||
Prevent Access To VBA Editor? | Excel Programming |