PDA

View Full Version : [SOLVED] UserForm RowSource Named Range Crashes



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

SamT
09-08-2016, 03:49 PM
Sheets("UserTable").ListObjects("tblUsers").ListColumns(2).DataBodyRange.Find(txtAdd.Text, LookIn:=xlValues) Returns a Range Object. If the Range is not found,ie Is Nothing, checking the value causes serious errors.


Dim Found As Range
Set Found = TlName.DataBodyRange.Find(txtAdd.Text, LookIn:=xlValues)
If Not Found Is Nothing Then
If Found = Strname then MsgBox "That name already exists, Try another", vbInformation, "Just a moment"
Else
You won't need the On Error GoTo, possibly an On Error Resume Next?

PS: I use Excel 2002, so I have no knowledge of "DataBodyRange."

snb
09-09-2016, 12:29 AM
Why describing a file you can upload ?

CarterJ
09-09-2016, 03:41 AM
Thanks SamT, I do prefer that method but it seems it was not the problem.

I spent a lot of time researching this and today I found that the 64bit version of MSExcel (and MSWord) on windows 10 is a bit buggy.
The gist of it is simply use the full syntax and stay away from defaults. (This was after all the reinstalls, fixes and repairs Microsoft suggested)

My line, "Set Rng = Tro.Add.Range" had to be written as "Set Rng = Tro.Add(AlwaysInsert:=True).Range"
The syntax is .add(position,Always insert: =(T/F)). It works fine when there is some content in the brackets but crashes excel without.
Very strange.

in reply to snb, I thought it best not to attach a file with unknown bugs.

Many thanks folks

CarterJ
09-10-2016, 02:12 PM
Thanks SamT, I do prefer that method but it seems it was not the problem.

I spent a lot of time researching this and today I found that the 64bit version of MSExcel (and MSWord) on windows 10 is a bit buggy.
The gist of it is simply use the full syntax and stay away from defaults. (This was after all the reinstalls, fixes and repairs Microsoft suggested)

My line, "Set Rng = Tro.Add.Range" had to be written as "Set Rng = Tro.Add(AlwaysInsert:=True).Range"
The syntax is .add(position,Always insert: =(T/F)). It works fine when there is some content in the brackets but crashes excel without.
Very strange.

in reply to snb, I thought it best not to attach a file with unknown bugs.

Many thanks folks

It seems the fix was temporary and the problem returned. I ended up following the listrows.add issue to a dead end and the same for the.find method. I eventually found the answer. Its the row source of the combo box I had in the form I was using.

As it turns out, I had taken some advice from a forum to use a named range as the range for a second named range and enter that name in the combo box property.
The combo box property initially ignored my entries and because my range was a table (dynamic) I could not use a static range. The work around worked and populated my combo box. The problem came when I added or removed rows. The property did not refresh and excel crashed.

I have since changed my code on the form to add the row source on form>initialise and clear the row source on form>hide.

Lesson learnt: don't mess with work arounds and stick to valid code.

Note to the moderator: I was unsure if a comment on a solved post was possible or not. Let me know if there is a more acceptable method.

SamT
09-10-2016, 03:47 PM
I was unsure if a comment on a solved post was possible or not
That's actually what we prefer. It may help someone else in the future.

I will change the Thread Title for efficient searching.

SamT,
Moderator