ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,683
- Office Version
- 2007
- Platform
- Windows
Hi,
I have copied some code & changed the values that will be populated into the list box & column to search etc.
On my userform i have 5 textboxes where each search a specific column.
I also have a listbox.
Example.
TextBoxName i enter a name, listbox is populated, i select a name in the listbox & i am taken to that customer on the worksheet.
The above works for all other textboxes.
My problem is this code ive just copied when i select a name in the listbox i get a RTE13
When i debug it the line of code below is in yellow
I dont understand why all the others work & this one fails as i shouldnt need to alter anything ?
The copied is also supplied.
I have copied some code & changed the values that will be populated into the list box & column to search etc.
On my userform i have 5 textboxes where each search a specific column.
I also have a listbox.
Example.
TextBoxName i enter a name, listbox is populated, i select a name in the listbox & i am taken to that customer on the worksheet.
The above works for all other textboxes.
My problem is this code ive just copied when i select a name in the listbox i get a RTE13
When i debug it the line of code below is in yellow
I dont understand why all the others work & this one fails as i shouldnt need to alter anything ?
The copied is also supplied.
Rich (BB code):
Private Sub ListBox1_Click()
Dim rw As Long
Dim ws As Worksheet
Dim answer As Integer
Set ws = ThisWorkbook.Sheets("Database")
If ListBox1.ListIndex = -1 Then Exit Sub
rw = ListBox1.List(ListBox1.ListIndex, 3)
ws.Range("A" & rw).Select
Unload Me
answer = MsgBox("OPEN CUSTOMERS FILE IN MAIN DATABASE ?", vbYesNo + vbInformation, "OPEN DATABASE MESSAGE")
If answer = vbYes Then
Database.LoadData Sheets("DATABASE"), Selection.Row
Else
Unload DatabaseSearchForm
End If
End Sub
Rich (BB code):
Private Sub TextBoxKeyType_Change()
Me.Label1.Visible = False
Me.Label2.Visible = False
Me.Label3.Visible = False
Me.Label4.Visible = False
TextBoxKeyType = UCase(TextBoxKeyType)
Dim r As Range, f As Range, cell As String, added As Boolean
Dim sh As Worksheet
Set sh = Sheets("DATABASE")
sh.Select
With ListBox1
.Clear
.ColumnCount = 4
.ColumnWidths = "150;290;130;10"
If TextBoxKeyType.Value = "" Then Exit Sub
Set r = Range("C6", Range("C" & Rows.Count).End(xlUp))
Set f = r.Find(TextBoxKeyType.Value, LookIn:=xlValues, LookAt:=xlPart)
If Not f Is Nothing Then
cell = f.Address
Do
added = False
For i = 0 To .ListCount - 1
Select Case StrComp(.List(i), f.Value, vbTextCompare)
Case 0, 1
.AddItem f.Value, i
.List(i, 1) = f.Offset(, -2).Value
.List(i, 2) = f.Offset(, 13).Value
.List(i, 3) = f.Offset(, -1).Value
.List(i, 4) = f.Row
added = True
Exit For
End Select
Next
If added = False Then
.AddItem f.Value
.List(.ListCount - 1, 1) = f.Offset(, -2).Value
.List(.ListCount - 1, 2) = f.Offset(, 13).Value
.List(.ListCount - 1, 3) = f.Offset(, -1).Value
.List(.ListCount - 1, 4) = f.Row
End If
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> cell
TextBoxSearch = UCase(TextBoxSearch)
.TopIndex = 0
Else
MsgBox "NO ITEM WAS FOUND USING THAT INFORMATION", vbCritical, "DATABASE SHEET ITEM SEARCH"
TextBoxKeyType.Value = ""
TextBoxKeyType.SetFocus
End If
End With
End Sub
Last edited by a moderator: