VB Editor Access

[Ken McLennan]

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

 

[Harald Staff]

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\Excel\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

 

[Living the Dream]

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.Parent
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.Parent
If IsEditorInSync = True Then
Debug.Print
End If
End With
End Function