Solved: Display info from Module in msg box

[MrRhodes2004]

Group,

I have the following module:
[vba]Option Explicit
Private Sub Workbook_Open()
'Allow only me to open the file in a read/write
If Not Application.UserName = "any name here" Then
ThisWorkbook.ChangeFileAccess xlReadOnly
End If
End Sub[/vba]

This code changes the FileAccess status to read only if you are not the "creator".

I'd like to be able to run a Sub that will look at this module and tell me "any name here" in a message box. This is to help identify who "locked" the worksheet.

Any direction would be helpful.

Thanks!

[mvidas]

Mr Rhodes,
[vba]Sub ShowWhoLockedIt()
Dim i As Long, j As Long, tStr As String
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
tStr = .Lines(1, .CountOfLines)
End With
i = InStr(1, tStr, "application.username = """, vbTextCompare) + 24
j = InStr(i, tStr, """", vbTextCompare)
MsgBox Mid(tStr, i, j - i)
End Sub[/vba]Matt

EDIT: You may want to consider using something other than 'Application.UserName', as that can be set by the user and they could change theirs to match yours and get into the file. See http://vbaexpress.com/kb/getarticle.php?kb_id=768 for your options

[Bob Phillips]

Can someone explain to my simple brain how this tells you who has the file locked?

[mvidas]

[MrRhodes2004]

Matt - thanks. That worked exactly as needed. It really isn't for security. It is more for the user that isn't paying attention (and makes changes) or who doesn't have respect for your work. The intent is to all others to open the workbook and automatically switch it to read only if they are not the creator. This allows the 'creator' full access at all time. Yes, it is easily gone around but most of the users in my group are not familiar with working of excel or their computers to the degree which may cause me trouble.

XLD - LOL. This first code is placed into a file via:
[VBA]Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Private Sub Workbook_Open()" & Chr(13) & _
"'Allow only me to open the file in a read/write" & Chr(13) & _
"If Not Application.UserName = """ & sUserName & """ Then " & Chr(13) & _
"ThisWorkbook.ChangeFileAccess xlReadOnly" & Chr(13) & _
"End If" & Chr(13) & _
"End Sub"
End With[/VBA]

Then the code produced by Matt will search the code placed for the "user name".

It is by far a fool proof system but for now it will work. I'm just trying to prevent other engineers from having my files open (to link to them - or whatever) or from modifying them. If they intentionally force the file to a read/write status after I have 'locked' it, I have other problems.