Help trapping a runtime error VBA

[AD_Taylor]

Hi,

I have the 2 code modules below. One initialises some data from a User Form and then sends it to a function in order to check passwords and return the data to the UserForm to handle.

Strangely it works fine on my computer at work (running Excel 2010) but when tried at home on my Mac (with Excel 2011 for Mac) the same code creates a run time error without the option to debug.

After stepping through it, it seems to occur randomly. If I let the code run through, the error is created just before the temporary form is shown (so all code is added to the temp form). But when I step into it the error is thrown up when adding code to the temp form and only 1 line of code is added.

Any help in trapping this would be really appreciated!

Code - UserForm:

Option Explicit

Function initAdminPass() As String

initAdminPass = GetTrackerSettings("adminPass")

End Function

Private Sub StuNamesBTN_Click()

'Unload Me
'UFStuNames.Show

End Sub

Private Sub AdminBTN_Click()

Dim adminPass As String
Dim subjPass As String

Dim passCorrect As String

adminPass = initAdminPass()
subjPass = ActiveSheet.Range("B1").Value

passCorrect = UserFormsController.GetPassWord("Enter a password...", adminPass, subjPass)

If passCorrect = "password1Ok" Then

    Unload Me
    UFAdmin.Show

ElseIf passCorrect = "password2Ok" Then

    Unload Me
    UFSubjectAdmin.Show

ElseIf passCorrect = "cancelOk" Then

    

Else

    Dim msgBox1 As Long
    msgBox1 = MsgBox("Incorrect password entered. Please check your spelling and try again.", vbOKOnly, titleName)

End If

End Sub

Code - Function:

 Function GetPassWord(Title As String, passToCheck As String, passToCheck2 As String) As String
'---------------------------------------------------------------------------------------
' Procedure : GetPassWord
' DateTime : 4/02/02 19:04
' Author : Ivan F Moala
' Purpose : Creates a Dynamic UF to Test for aPassword
' : so there is no need to create one.
'---------------------------------------------------------------------------------------
Dim TempForm
Dim NewTextBox As MSForms.TextBox
Dim NewCommandButton1 As MSForms.CommandButton
Dim NewCommandButton2 As MSForms.CommandButton
Dim x As Integer

password1 = passToCheck
password2 = passToCheck2

' Hide VBE window to prevent screen flashing
'Application.VBE.MainWindow.Visible = False

' Create a Temp UserForm
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)

' Add a TextBox
Set NewTextBox = TempForm.Designer.Controls.Add("forms.textbox.1")
With NewTextBox
.PasswordChar = "*"
.Width = 140
.Height = 20
.Left = 48
.Top = 18
End With

' Add the Ok button
Set NewCommandButton1 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton1
.Caption = "OK"
.Height = 18
.Width = 66
.Left = 126
.Top = 66
End With

' Add the Cancel button
Set NewCommandButton2 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton2
.Caption = "Cancel"
.Height = 18
.Width = 66
.Left = 30
.Top = 66
End With

' Add event-handler subs for the CommandButtons & Userform
With TempForm.CodeModule
x = .CountOfLines
.insertlines x + 1, "Sub CommandButton2_Click()"
.insertlines x + 2, "cancelOk = True: Unload Me"
.insertlines x + 3, "End Sub"

.insertlines x + 4, "Sub CommandButton1_Click()"
.insertlines x + 5, "If TextBox1 = password1 Then password1Ok = True"
.insertlines x + 6, "If TextBox1 = password2 Then password2Ok = True"
.insertlines x + 7, "Unload Me"
.insertlines x + 8, "End Sub"

.insertlines x + 9, "Private Sub UserForm_Initialize()"
.insertlines x + 10, "Application.EnableCancelKey = xlErrorHandler"
.insertlines x + 11, "End Sub"
End With

' Adjust the form
With TempForm
.Properties("Caption") = Title
.Properties("Width") = 240
.Properties("Height") = 120
NewCommandButton1.Left = 46
NewCommandButton2.Left = 126
End With

' Show the form
VBA.UserForms.Add(TempForm.Name).Show

' Delete the form
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm

' Pass the Variable back to the calling procedure
If password1Ok = True Then

    GetPassWord = "password1Ok"
    
ElseIf password2Ok = True Then

    GetPassWord = "password2Ok"
    
ElseIf cancelOk = True Then

    GetPassWord = "cancelOk"
    
Else

    GetPassWord = "False"
    
End If

End Function