In order to be able to add or change Gatekeeper protection rules it is preferable that you (i) trust/enable programmatic access to the VBA Project Object Model, and (ii) unprotect VB projects. Once the rules are set Gatekeeper will enforce most of the protection rules even without these two requirements being met, however, some functionality might be disabled or not work as expected.
Gatekeeper stores protection rules by the worksheet’s codename. This is because the codename, unlike the worksheet’s name, cannot be changed (through Excel’s interface). However, there might be instances, when because of the way the codename is handled by Excel/VB where it is not yet instantiated/defined. When this occurs Gatekeeper tries to trigger its instantiation programmatically, but if the above requirements are not met it will fail and will not allow you to create new protection rules for the affected worksheets.
The following simple procedure can be used to give you a quick demonstration of when a worksheet’s codename might not be available.
Public Sub showCodeNames()
Dim wsht As Worksheet
Dim strMsg As String
Dim blnWarning As Boolean
strMsg = vbCrLf
For Each wsht In Worksheets 'read thisworkbook's worksheets name and codename
strMsg = strMsg & "Name: '" & wsht.Name & "' :: Codename: '" & wsht.CodeName & "' " & vbCrLf
If Len(wsht.CodeName) = 0 Then blnWarning = True
MsgBox strMsg & vbCrLf, vbOKOnly + IIf(blnWarning, vbExclamation, 0), "Codenames"
When run, the above code will display the names of all worksheets within the workbook together with their respective codename. By following these steps you will be shown when the codename is not available:
- Copy the code;
- Open a new workbook, open VBE (Alt+F11) and paste the code in a new module;
- Close VBE;
- Run the code (Alt+F8) and it will display a list of worksheet names and codenames. This time all worksheets should have a corresponding codename;
- Close the message box;
- Add a new worksheet (make sure VBE is closed) and re-run the code. This time the codename for the added worksheet will be blank (‘’);
- If you open and close VBE, and re-run the code again the new worksheet will have a codename assigned.
The problem with this behavior is that since Gatekeeper stores rules by the worksheet’s codename it must ensure each worksheet codename is available, and if not, try to enforce the instantiation of the codename. To do that programmatically it needs to have both access to the VBA Project Object Model, and the respective workbook’s project not being protected (otherwise a runtime error is generated denying the operation). If it fails to trigger the codename instantiation Gatekeeper will exclude those worksheets (without a codename) from the worksheet lists.
A possible workaround for this is that of opening and closing the VBE (Alt-F11) before setting or changing any Gatekeeper protection rules. If you choose this option, however, there might still be instances where some Gatekeeper functionality will be disabled or not work as expected when it comes to enforcing the rules.