0

Is it possible in some way to have access to windows credentials, to know which user wants to occupy the file and thus facilitate or deny access ?. I have a code that searches for the user and according to that gives or removes access, but it seems unreliable. The code is the following:

Private Sub Workbook_Open()
ThisWorkbook.Windows(1).Visible = False
Application.DisplayAlerts = False

If Application.UserName = "CMSTORM" Or Application.UserName = "User2" Then
    Welc = MsgBox("Bienvenido " & Application.UserName)
    ThisWorkbook.Windows(1).Visible = True
    Application.DisplayAlerts = True
    Exit Sub
Else
    Pass = "1973"
    Prompt = "Ingresa la contraseña para continuar"
    Title = "Ingreso de Contraseña"
    UserPass = InputBox(Prompt, Title)
    If UserPass <> Pass Then
        Prompt = "Contraseña Incorrecta"
        Title = "Contraseña Incorrecta"
        MsgBox Prompt, vbCritical, Title
        ThisWorkbook.Close
        Exit Sub
    Else
    Welc = MsgBox("Bienvenido " & Application.UserName)
    ThisWorkbook.Windows(1).Visible = True
    Application.DisplayAlerts = True
    End If
End If
End Sub
Armas.Chuy
  • 17
  • 8
  • 5
    Might be easier to manage permissions for the storage folder on the NAS or SharePoint. – teylyn Feb 12 '20 at 19:51
  • 3
    Excel should never be assumed to be secure. Hard-coding the password is a [bad idea](https://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project) :-) – BigBen Feb 12 '20 at 19:52
  • Yeah, it's not that "important" info but still, and yeah the server has permissions and stuff, i was just wondering whether it can be possible. – Armas.Chuy Feb 12 '20 at 20:05
  • 1
    `Application.UserName` is whatever the user decides to put in the "User name" field of Excel's options dialog. It has nothing whatsoever to do with any Windows credentials and should absolutely not be used for anything that means to be secure. Actually, *VBA* shouldn't be used for anything that means to be secure. Is this access denial a convenience thing or an actual security requirement? – Mathieu Guindon Feb 12 '20 at 20:06
  • No, as i said before the company's server has the security it's just a convenience thing and curiosity. But thanks for the concern. I will read it thank you. – Armas.Chuy Feb 12 '20 at 20:09

1 Answers1

1

For a variety of reasons (lack of auto-execute for security reasons, mostly though), it is impossible to restrict file viewing (even at a worksheet level) with straight vba. As many users have stated, you should be using file permissions at the server level. In addition, it might make sense to use a password on your sheets, though note that this can be easily broken through.

That said, you can infact get the current user from windows. This is done by calling the windows API from within vba. https://renenyffenegger.ch/notes/development/languages/VBA/Win-API/examples/GetUserName specifically goes into what you are attempting to do

delyeet
  • 168
  • 9