CarterJ
09-08-2016, 12:16 PM
I have tried various things but I am sure its my error handling.
So the overview, I am building an exercise program for my son's maths class. It offers reporting so a user management process is required.
I have a table in a hidden sheet with 4 columns (Date added, Name, ID, active flag).
On worksheet open I check if the table is empty and offer an input box for the first user or return the user with an active flag on existing users.
All good there.
I have a button for user management that shows a form with a multipage Create, Change and Delete option.
The user enters text in the text box and clicks add.
The code does two things, 1) Check for blank submission and 2) find the text in the name field and either show a message box that it already exists or adds the new username. I have tried various methods and alternatives to error handling on the .find method but excel just crashes each time.
I understand that if the text is not found, matched or returned it will pass an error but why crash?
It works fine run from the code but not as part of the opening process.
I have reinstalled office but no change. perhaps fresh eyes will see my error.
My code is as follows:
Private Sub cmdAdd_Click()
Dim Sho As Worksheet
Dim Tlo As ListObject
Dim Tro As ListRows
Dim Tor As ListRows
Dim TlName As ListColumn, TlId As ListColumn, TlFlag As ListColumn
Dim Rng As Range
Dim Strname As String
Set Sho = Sheets("UserTable")
Set Tlo = Sho.ListObjects("tblUsers")
Set Tro = Tlo.ListRows
Set TlName = Tlo.ListColumns(2)
Set TlId = Tlo.ListColumns(3)
Set TlFlag = Tlo.ListColumns(4)
Strname = Me.txtAdd
'Test for Blank input
If Strname = "" Then
MsgBox " You must enter a valid name", vbExclamation, "The Name is Blank!"
Exit Sub
End If
'Test for duplicates
On Error GoTo Alt
If TlName.DataBodyRange.Find(txtAdd.Text, LookIn:=xlValues) = Strname Then
MsgBox "That name already exists, Try another", vbInformation, "Just a moment"
Else
Alt:
Set Rng = Tro.Add.Range
Rng(1, 1).Value = Now()
Rng(1, 2).Value = Strname
Rng(1, 3).Value = WorksheetFunction.Max(TlId.DataBodyRange) + 1
TlFlag.DataBodyRange.ClearContents
Rng(1, 4).Value = 1
End If
Me.txtAdd.Text = ""
Me.cboChange.Text = ""
Me.cboDelete.Text = ""
Me.Hide
Sheets("Index").Activate
End Sub
So the overview, I am building an exercise program for my son's maths class. It offers reporting so a user management process is required.
I have a table in a hidden sheet with 4 columns (Date added, Name, ID, active flag).
On worksheet open I check if the table is empty and offer an input box for the first user or return the user with an active flag on existing users.
All good there.
I have a button for user management that shows a form with a multipage Create, Change and Delete option.
The user enters text in the text box and clicks add.
The code does two things, 1) Check for blank submission and 2) find the text in the name field and either show a message box that it already exists or adds the new username. I have tried various methods and alternatives to error handling on the .find method but excel just crashes each time.
I understand that if the text is not found, matched or returned it will pass an error but why crash?
It works fine run from the code but not as part of the opening process.
I have reinstalled office but no change. perhaps fresh eyes will see my error.
My code is as follows:
Private Sub cmdAdd_Click()
Dim Sho As Worksheet
Dim Tlo As ListObject
Dim Tro As ListRows
Dim Tor As ListRows
Dim TlName As ListColumn, TlId As ListColumn, TlFlag As ListColumn
Dim Rng As Range
Dim Strname As String
Set Sho = Sheets("UserTable")
Set Tlo = Sho.ListObjects("tblUsers")
Set Tro = Tlo.ListRows
Set TlName = Tlo.ListColumns(2)
Set TlId = Tlo.ListColumns(3)
Set TlFlag = Tlo.ListColumns(4)
Strname = Me.txtAdd
'Test for Blank input
If Strname = "" Then
MsgBox " You must enter a valid name", vbExclamation, "The Name is Blank!"
Exit Sub
End If
'Test for duplicates
On Error GoTo Alt
If TlName.DataBodyRange.Find(txtAdd.Text, LookIn:=xlValues) = Strname Then
MsgBox "That name already exists, Try another", vbInformation, "Just a moment"
Else
Alt:
Set Rng = Tro.Add.Range
Rng(1, 1).Value = Now()
Rng(1, 2).Value = Strname
Rng(1, 3).Value = WorksheetFunction.Max(TlId.DataBodyRange) + 1
TlFlag.DataBodyRange.ClearContents
Rng(1, 4).Value = 1
End If
Me.txtAdd.Text = ""
Me.cboChange.Text = ""
Me.cboDelete.Text = ""
Me.Hide
Sheets("Index").Activate
End Sub