I have created a User Registration procedure to nag users of Excel Workbooks I create. A working version is attached as “TEST – User Registration.xlsm”.
Basically the process is that as a Workbook opens the Excel VBA Workbook_Open event in ThisWorkbook triggers and runs the following subroutine:
Private Sub Workbook_Open()
‘ #### Procedure to check User and Computer Registration and to
‘ display the nag “About” form the first time used by user or on a computer
‘ Creator: BygAuld.Byrd
‘ Date: 1 December 2016
Dim Msg, Button, Title, Response As String
Button = vbExclamation
Title = “Private Sub Workbook_Open()…”
‘ Msg = “…”
‘ Response = MsgBox(Msg, Button, Title)
On Error GoTo ErrorMessages
‘ Screen updating OFF
Application.ScreenUpdating = False
‘ #### Check User Registration
[B]Call User_Registration[/B]
‘ **** END Check User Registration
‘ Screen updating ON
Application.ScreenUpdating = True
Exit Sub
ErrorMessages:
Msg = “There is an error in ” & Title & vbCrLf & vbCrLf & _
“Error: ” & Err & ” ” & Err.Description
Response = MsgBox(Msg, Button, Title)
End Sub
This subroutine then calls the “Public Sub User_Registration()” contained in “Module_User_Registration”. Here is its code:
Public Sub User_Registration()
‘ Procedure to register user and computer and display a nagging “About” form
‘ the first 5 times the Workbook is opened by a user or computer
‘ Creator: BygAuld.Byrd
‘ Date 1 December 2016
Dim RegisteredUser As String
Dim ThisUser As String
Dim RegisteredComputer As String
Dim ThisComputer As String
Dim shtHome As Worksheet
Dim strHomeSheet As String
Dim shtParameters As Worksheet
Dim Msg, Button, Title, Response As String
Button = vbExclamation
Title = “Public Sub User_Registration()…”
‘ Msg = “…”
‘ Response = MsgBox(Msg, Button, Title)
On Error GoTo ErrorMessages0
With Application
‘ Hide Excel
.Visible = False
‘ Screen updating OFF
.ScreenUpdating = False
End With
‘ Get the name of the “Home” from the “Parameters” sheet
Set shtParameters = ThisWorkbook.Sheets(“Parameters”)
strHomeSheet = shtParameters.Range(“HomeSheet”)
‘ Get The current User Name and Computer Identification
ThisUser = Application.UserName
ThisComputer = CreateObject(“Scripting.FileSystemObject”).GetDrive(“C:”).SerialNumber
On Error GoTo ErrorMessages1
Set shtHome = Sheets(strHomeSheet)
On Error GoTo ErrorMessages0
With shtParameters
‘ Get existing Registered User and Registered Computer details
RegisteredUser = .Range(“RegisteredUser”)
RegisteredComputer = .Range(“RegisteredComputer”)
‘ #### Load About dialogue on opening the first 5 times the Workbook is opened
‘ Check if this is a new User or Computer and initialise the Registration parameters
‘ then load the “About” form
If RegisteredUser ThisUser Or RegisteredComputer ThisComputer Then
.Range(“RegisteredUser”).Value = ThisUser
.Range(“RegisteredComputer”).Value = ThisComputer
.Range(“OpeningCount”).Value = 0
‘ Load “About” form
Call LoadUDF(UserForm_About)
Else
‘ If Registered User or Computer has opened less than 6 times Nag with “About” form
If .Range(“OpeningCount”).Value < 6 Then
.Range("OpeningCount") = .Range("OpeningCount") + 1
' Load "About" form
Call LoadUDF(UserForm_About)
End If
End If
' Hide Parameters sheet
' .Visible = xlSheetHidden
End With
' **** END Load About dialogue on opening the first 5 times the Workbook is opened
' #### Always open workbook at "Home" sheet
With shtHome
.Select
.Activate
.Range("B5").Select
End With
' **** END Always open workbook at Summary sheet
' *******************
' Cleanup then quit sub
Cleanup:
Set shtHome = Nothing
Set shtParameters = Nothing
With Application
' Screen updating ON
.ScreenUpdating = True
' Unhide Excel
.Visible = True
End With
Exit Sub
ErrorMessages0:
Msg = "There is an error in " & Title & vbCrLf & vbCrLf & _
"Error: " & Err & " " & Err.Description
Response = MsgBox(Msg, Button, Title)
GoTo Cleanup
ErrorMessages1:
Msg = "There is an error in " & Title & vbCrLf & vbCrLf & _
"Error: " & Err & " " & Err.Description & vbCrLf & vbCrLf & _
"The Home Sheet Name set in Parameters sheet does not" & vbCrLf & _
"the name of a sheet in this workbook."
Response = MsgBox(Msg, Button, Title)
With ThisWorkbook.Sheets("Parameters")
.Visible = xlSheetVisible
.Activate
.Range("HomeSheet").Select
End With
GoTo Cleanup
End Sub
Within the Workbook is a sheet, normally hidden, called “Parameters”, in which there are named ranges that contain the details used by the “Public Sub User_Registration()” subroutine.
This process works perfectly in a number of Workbooks I have created, including “TEST – User Registration.xlsm” but crashes Excel 2010 (fully updated) every time I attempt to open “TEST Coincidence Analyser (Call User Registration ON).xlsm”, resulting in the following sequence of error dialogues:
The “TEST Coincidence Analyser (…).xlsm” contains many other VBA subroutines, all of which appear to work perfectly and should not influence the User Registration function, except “Public Sub LoadUDF(UserForm_Name As Object)” which it in turn calls. The exact same User Registration subroutines are used in other Workbooks with many more, and more complicated, VBA subroutines and they all work perfectly. It’s just this particular workbook that is causing me these errors.
What I’ve tried:
I’ve tried creating a completely new workbook and copying in the relevant subroutines but the problem persists.
Testing with “TEST Coincidence Analyser (Call User Registration OFF).xlsm” and manually running the triggered sub-routines produces the following results:
-
[*]Manually running the “Private Sub Workbook_Open()” subroutine from within VBA produces the following error dialogue:
-
[*]Manually running the “Public Sub User_Registration()” subroutine from within VBA does not produce any error.
Attached files:
I’ve the attached “TEST – User Registration.xlsm” along with two (2) versions of “TEST Coincidence Analyser (…).xlsm”:
-
[*]one with the call to “User-Registration” active, and
[*]the other with that call commented out for testing purposes.
The offending files are, respectively “TEST Coincidence Analyser (Call User Registration ON).xlsm” and “TEST Coincidence Analyser (Call User Registration OFF).xlsm”.
All attached files have been ZIPped into a single attachment “TEST – User Regitration.ZIP”. All “.XLSM” files have the added file extension “.ZZZ” to prevent them from running inadvertently. It’s up to anyone wishing to try to resolve this issue with me to remove the “.ZZZ” before trying to run the files.
Any clues on how to resolve this issue will be very much appreciated.
In anticipation, grateful thanks.
Cheers
Trevor
