Solved: Need Help with Protection Scheme

[stanl]

I am, and will be creating workbooks with large amounts of data placed in pivot tables or holding survey results... really lots of stuff.

These need to be made available on a network folder with the normal caveats of users not making changes or using save_as. Rather than going the route of readonly attributes, sheet passwords I Googled code for Workbook_Open and Before close events which (1) disable menus and toolbars (2) close the workbook without saving changes (3) disable Alt_F8 and set password for Alt_f11 [which also re-enables everything, so I can update]. What I want to do is write a procedure to apply this schema to any given workbook. Roughly...


Sub protect_wkb()
Dim WB As Workbook

f = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Please Select Import File")

If f = False Then Exit Sub

Application.Workbooks.Open Filename:=f

Set WB = ActiveWorkbook

'this is where I need help:
'this code, originally posted by Ivan sets the VBProject Password
'so users cannot view code.

Call Change_VBA_PW(WB)

WB.Close True
End Sub


In the
'this is where I need help: section, I need to insert the contents of the 2 attached text files into the workbook code, a standard module. The workbooks I will apply this schema to may or may not have macro code, but none will have exisiting workbook event code.

Admittedly, this is a hack and not refined, but if I can get help inserting the text files, refinements will follow.

Stan
Edited 29-Jun-07 by geekgirlau. Reason: insert line breaks

[mvidas]

Hey Stan, 'this is where I need help:
With WB.VBProject.VBComponents
.Import "C:\altf11.txt"
.Item("ThisWorkbook").CodeModule.AddFromFile "c:\protect_wkb.txt"
End With

I'd also recommend adding something to force users to enable macros while using the workbook.. for example if you add this variable to the ThisWorkbook object and add this Set statement to your Workbook_Open sub in the protect_wkb.txt:Dim vForceMacros As clsForceMacros
Sub Workbook_Open()
Set vForceMacros = New clsForceMacros
Dim a As CommandBar
'rest as you have it Then add this to the end of that file so it gets added into ThisWorkbook too:Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If vForceMacros Is Nothing Then
Set vForceMacros = New clsForceMacros
vForceMacros.Save SaveAsUI, Cancel
End If
End Sub
Then you could add this to my original With Block in the code above: .Import "C:\clsForceMacros.cls"
Which refers to the attached file

[stanl]

Thanks - forgot all about .import

fatal flaw with my scheme - if Macro Security is set to anything but low the scheme is toast, your enable macros code is interesting but want save and save as disabled; also in several of my workbooks an number of sheets (contining lookup data) will be hidden...

Back to the drawing board:banghead:

[stanl]

Matt,

I incorporated your basic idea - pretty slick. I attached the updated text files, protect_wkb to run the macro from and book1 to play with. You rock, man:thumb Stan

[mvidas]

(gets ready to duck) Glad to help, but this is quite breakable :)
I just ran it on Book1.xls that you included in the zip file.. opened book1 up, worked as described, closed it, still worked as described (might want to add something for screenupdating though).

Went to windows explorer, copied Book1.xls into same directory (creating Copy of Book1.xls). Opened both up, closed the Copy. Book1 was still open, toolbars and everything have full use.

Maybe play around with workbook_activate and workbook_deactivate?

[stanl]

Maybe play around with workbook_activate and workbook_deactivate?

Nah! This gets the job done for now. My first time trying it with VBA; I have a Winbatch compiled EXE I did circa 2004 - streams the workbooks into binary fields in an .adt recordset - the streams them back out to a temp.xls file when needed, everything is controlled through COM no VBA code. The user gets a workbook, no menus or toolbars, but Winbatch displays a floating toolbar to print/close the workbook. If the user click on the [x] in the right-hand corner, Winbatch detects the handle is no longer valid, in either case the temp.xls is erased - I originally designed it for a small firm doing stock portfolios and who didn't want workbooks around.

Stan

[stanl]

P.S. .. if I disable File | Open [ID 23] I can't be in 2 places at once...


[so I'm not anywhere at all]...............................:rotlaugh:

[mvidas]

Does that prevent a file from being opened from windows explorer or an email attachment/etc?

[stanl]

Does that prevent a file from being opened from windows explorer or an email attachment/etc?

hard to say w/out testing I s'pose... but the real killer is if the user does not have Trusted Security for VB Projects checked. You get around that with a registry hack, and that is why I moved from VBA back to Winbatch. I can check/set/reset Trusted Security, then actually insert the macro code from WB memvars, and determine if protection was applied to the target workbook or not.

I'm sure it can be broken, but I'm also glad it can be easily built.