Writing dynamic code - problem with VBA project protection

[pauly]

Hi people

I've written a function that writes another function in a module.
When i run it the VB editor appears - which i dont want to happen, as the
underlying code is of no interest to the users.

Can i either close it dynamically or stop it from opening?

I think the problem is even worse than this because the finished spreadsheet
really needs to be tamper proof (for that read "idiot-proof" - obviously
anyone who knows what they're doing should be able to get in).

When the VBA project is protected it comes back with run-time error 50289
"Can't perform operation since the project is protected". And as it appears
that i can't programmatically unprotect the project:
http://www.j-walk.com/ss/excel/faqs/protectionFAQ.htm#P3
... then it looks like i'm done for?

There must be some workaround surely?


Thanks in advance

Paul

 

[Guest]

Hi
some code workarounds for you (esp. for the first issue I'm also looking for
something better)
1. VBE Editor:

sub foo()
dim oState
oState = Application.VBE.MainWindow.Visible
'...
'some code
'...
If Not (oState) Then
Application.VBE.MainWindow.Visible = False
End If
end sub

2. VBA project protected: Some code to check if it is protected
'---------------------------------------------------------------------------
Private Function VBAccessAllowed()
'---------------------------------------------------------------------------
' Function : Test VBA security settings to see if we can modify
' VBA code (caused by A new security setting in
' Excel 2002: Trust access to Visual Basic Project
' Synopsis: If Version >= 10
' Try and set an object to a workbook project and
' test whether it works or not.
' If fail, show messgae and return False.
' Returns: True/False - default True
'---------------------------------------------------------------------------

Dim VBProject As Object ' as VBProject
Dim sMsg As String

VBAccessAllowed = True
If Val(Application.Version) >= 10 Then
On Error Resume Next
Set VBProject = ActiveWorkbook.VBProject
If Err.Number <> 0 Then
sMsg ="Project protected"
msgbox sMsg
VBAccessAllowed = False
End If
End If

'clear variables
Set VBProject = Nothing
End Function