Checking Excel's Filename

This is an improvement of my previous post. It always happens to me that whenever I put a password protection in my Excel/VBA program, I need to hide to the regular users not just the confidential data or customized menu, but the password window also should not displayed so that they will not have the idea that the file has a confidential information or has features that they shouldn't see.

This is a simple trick that I've been using for years. Open the program that we did in the last post and change code in Open event in the ThisWorkbook object as indicated below:

'this is the original code

Private Sub Workbook_Open()
PasswordOk = False
frmPassword.Show
End Sub


'change it to this one

Private Sub Workbook_Open()
PasswordOk = False
If Left(ThisWorkbook.Name, 5) = "SYSAD" Then
   frmPassword.Show
End If
End Sub

As you can see the program simply checks the filename of the Excel file if it is prefixed by the word "SYSAD". If it is, it will prompt for the password, otherwise, it loads just like an ordinary Excel file.

To ordinary users of the file you can just send to them a copy of the file without the prefix "SYSAD". To all users that needs to have access to the protected worksheet and customized menu, you can send to them the file with "SYSAD".

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.