MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Block users macro needed


Posted by Greg Galla on July 24, 2001 9:07 AM

Can someone help me write a macro that will do the following: only allow certain users to use a spreadsheet. I would like it to pull from the Options/General/User name.

For example: Under tools/options/general/user name is "Greg Galla"

When I open a certain spreadsheet, the spreadsheet should recognize my name and allow me to read/write the spreadsheet. If the spreadsheet does not recognize my name, it should allow me to only open the spreadsheet read only. I would like to bypass all the password prompts. Let me know if you need further clarification. Thanks in advance


Posted by Jerid on July 25, 2001 6:48 AM

Hi Greg, here is one way you could do it.


Put this code in your ThisWorkBook module


Public bReadOnly As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If bReadOnly = True Then
Application.ActiveWorkbook.Saved = True
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If bReadOnly = True Then
Cancel = True
End If
End Sub

Private Sub Workbook_Open()
If Application.UserName = "Greg Galla" Then
bReadOnly = False
Else
bReadOnly = True
End If
End Sub

Jerid