Writing dynamic code - problem with VBA project protection

  • Thread starter Thread starter pauly
  • Start date Start date
P

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
 
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
 
Back
Top