User Access Control

For years, nearly all networks have some element of User Access Control, whether it be to the network as a whole, or to certain parts of it, i.e. particular network files, or particular applications. Even internet and Wi-Fi providers provide a user name and password to access their service, although in many cases this is not restricted and/or secure, but that is for a different website.

Network security usually falls to one or a small group of IT Administrators, and they are in charge of making sure the network is only used by authorised users, this usually involves granting access to particular parts of the network, which is done by a setup utility on the network operating system. Applications can sometimes have their own user access control, usually by having a logon screen built into them, which then control what parts of the application they have access to.

Databases are easy to apply access controls to, SQL Server Databases have to have a user mapped to that database so they can make a connection to it, and this is done using the Server Management tool. In Access, there is a User Level Security module built-in, which can be used to grant users access to certain Forms, Tables, Queries, and Reports. You could also compile an Access database into an Access Executable, which prevents users modifying Forms, Reports, and VBA Code, but leaves Tables and Queries, so data can be added and/or modified.

In Excel though, User Access Control is less easy, in fact quite complex. For a start, there isnít the means to restrict certain users in Excel itself in the same way as you can with databases, there are however ways and means to protect certain parts in different ways. This ranges from protecting certain parts of a worksheet, protecting the structure of a workbook, adding a password to modify a workbook, or a password to open the workbook.

This means there are options depending on the requirements. For example, you may wish the file be opened by anybody to add data to, in which case you would leave the necessary sheets unprotected, maybe protect others so they canít be changed, then set a password to protect the workbook. In other cases, you might want anybody to be able to open the workbook, but only certain users to be able to make changes to it, in which case you would set a password to modify when it is saved.

There are ways to set user access in Excel with VBA; this can be done in one of two ways, either by having a worksheet that has a space for someone to type a username and password, with a Command Button, once this is pressed, a piece of code could be run this checks this from a list in an external file, or on a hidden sheet in the workbook. The other way is to check the Name of the user logged into Windows, and use this to check against a list.

Once the user name has been checked, the function could close the workbook if the username is not in the authorised list, or if the username exists in the list, it could unhide and/or unprotect other sheets. In fact, if you have a list of users with levels of access, this could also be used to unhide/unprotect certain sheets.

This is just an outline of User Access Control, more detailed articles for Access, Excel, and VB, can be found in the relevant section.