onmyway
02-09-2015, 03:41 AM
hi guys,
i hope you can help.
I get a run-time error 424: Object required in the following line of my search function (down below):
GlobalRowCount = .AutoFilter.range.Columns(1).SpecialCells(xlCellTypeVisible).count - 1
How it works:
i type my reference in the TextBox (txtSearch), which then lists possible matches in ListBox3.
i then double click on the preferred record in ListBox3 to populate my UserForm. it is at this stage that I get the error....
'SEARCH FUNCTION
Private Sub txtSearch_Change()
Dim rng As range, e
Dim Ans As Long
With Me
.ListBox3.Clear
If Len(.txtSearch.Value) Then
For Each e In Sheets("DataBase").Cells(1).CurrentRegion.Columns(133).Offset(1).Value
If (e <> "") * (e Like "*" & .txtSearch.Value & "*") Then
.ListBox3.AddItem e
End If
Next
With .ListBox3
If .ListCount > 0 Then .ListIndex = 0
If .ListCount = 0 Then Ans = MsgBox("No Search reults found, continue?", vbYesNo)
If Ans = vbYes Then
If MsgBox("Would you like to create a new record?", vbYesNo) = vbYes Then
Call ActiveControls
End If
'Call btnNew_Click
Me.cbxEquipmentCommon.SetFocus
End If
End With
End If
End With
End Sub
'SEARCH FUNCTION
Private Sub ListBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim RowCount As Long
Dim numberOfColumns As Integer
numberOfColumns = 9
DetermineLastRow
'Clear the autofilter to ensure that our last row index remains corret
'cells.AutoFilter
'Get the last row to edit (this is for adding new rows when saving
'LastRowIndex = cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).row
With Sheets("DataBase").AutoFilterMode = False
With range("A1")
.AutoFilter
.AutoFilter field:=1, Criteria1:=ListBox3.Value
End With
'Get the corret row count
GlobalRowCount = .AutoFilter.range.Columns(1).SpecialCells(xlCellTypeVisible).count - 1
With UserForm1
Dim FirstRowAdd As String
Dim LastRow As Long, FirstRow As Long
FirstRowAdd = FirstVisibleValue(ActiveSheet, 1)
FirstRow = range(FirstRowAdd).row
.GUID.Value = Sheets("DataBase").Cells(FirstRow, 1).Value
' Me.TextBox19.Value = Sheets("DataBase").Cells(FirstRow, 2).Value
' Me.TextBox20.Value = Sheets("DataBase").Cells(FirstRow, 3).Value
' Me.TextBox21.Value = Sheets("DataBase").Cells(FirstRow, 4).Value
End With
End With
'Work on all the data in this section
Dim localData()
ReDim Preserve localData(GlobalRowCount - 1, numberOfColumns)
Dim currentRow As Integer
currentRow = 0
Dim currentCell As Integer
currentCell = 0
For Each row In range("RecordData").SpecialCells(xlCellTypeVisible).Rows
If (currentRow > GlobalRowCount) Then Exit For
'We should ignore the filter header
If (currentRow > 0) Then
Dim thisRange As range
Set thisRange = row.Cells.SpecialCells(xlCellTypeVisible)
Dim currentReference As RowReference
Set currentReference = New RowReference
currentReference.AllCells = thisRange
currentReference.CellCount = thisRange.count
currentReference.IsNew = False
currentReference.RowIndex = thisRange.row
Set GlobalData(currentRow - 1) = currentReference
Dim cellIndex As Integer
cellIndex = 0
For Each cell In thisRange
localData(currentRow - 1, cellIndex) = cell
cellIndex = cellIndex + 1
Next cell
End If
currentRow = currentRow + 1
Next row
'update list box
ListBox1.ColumnCount = numberOfColumns
ListBox1.Clear
ListBox1.List() = localData
End Sub
i hope you can help.
I get a run-time error 424: Object required in the following line of my search function (down below):
GlobalRowCount = .AutoFilter.range.Columns(1).SpecialCells(xlCellTypeVisible).count - 1
How it works:
i type my reference in the TextBox (txtSearch), which then lists possible matches in ListBox3.
i then double click on the preferred record in ListBox3 to populate my UserForm. it is at this stage that I get the error....
'SEARCH FUNCTION
Private Sub txtSearch_Change()
Dim rng As range, e
Dim Ans As Long
With Me
.ListBox3.Clear
If Len(.txtSearch.Value) Then
For Each e In Sheets("DataBase").Cells(1).CurrentRegion.Columns(133).Offset(1).Value
If (e <> "") * (e Like "*" & .txtSearch.Value & "*") Then
.ListBox3.AddItem e
End If
Next
With .ListBox3
If .ListCount > 0 Then .ListIndex = 0
If .ListCount = 0 Then Ans = MsgBox("No Search reults found, continue?", vbYesNo)
If Ans = vbYes Then
If MsgBox("Would you like to create a new record?", vbYesNo) = vbYes Then
Call ActiveControls
End If
'Call btnNew_Click
Me.cbxEquipmentCommon.SetFocus
End If
End With
End If
End With
End Sub
'SEARCH FUNCTION
Private Sub ListBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim RowCount As Long
Dim numberOfColumns As Integer
numberOfColumns = 9
DetermineLastRow
'Clear the autofilter to ensure that our last row index remains corret
'cells.AutoFilter
'Get the last row to edit (this is for adding new rows when saving
'LastRowIndex = cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).row
With Sheets("DataBase").AutoFilterMode = False
With range("A1")
.AutoFilter
.AutoFilter field:=1, Criteria1:=ListBox3.Value
End With
'Get the corret row count
GlobalRowCount = .AutoFilter.range.Columns(1).SpecialCells(xlCellTypeVisible).count - 1
With UserForm1
Dim FirstRowAdd As String
Dim LastRow As Long, FirstRow As Long
FirstRowAdd = FirstVisibleValue(ActiveSheet, 1)
FirstRow = range(FirstRowAdd).row
.GUID.Value = Sheets("DataBase").Cells(FirstRow, 1).Value
' Me.TextBox19.Value = Sheets("DataBase").Cells(FirstRow, 2).Value
' Me.TextBox20.Value = Sheets("DataBase").Cells(FirstRow, 3).Value
' Me.TextBox21.Value = Sheets("DataBase").Cells(FirstRow, 4).Value
End With
End With
'Work on all the data in this section
Dim localData()
ReDim Preserve localData(GlobalRowCount - 1, numberOfColumns)
Dim currentRow As Integer
currentRow = 0
Dim currentCell As Integer
currentCell = 0
For Each row In range("RecordData").SpecialCells(xlCellTypeVisible).Rows
If (currentRow > GlobalRowCount) Then Exit For
'We should ignore the filter header
If (currentRow > 0) Then
Dim thisRange As range
Set thisRange = row.Cells.SpecialCells(xlCellTypeVisible)
Dim currentReference As RowReference
Set currentReference = New RowReference
currentReference.AllCells = thisRange
currentReference.CellCount = thisRange.count
currentReference.IsNew = False
currentReference.RowIndex = thisRange.row
Set GlobalData(currentRow - 1) = currentReference
Dim cellIndex As Integer
cellIndex = 0
For Each cell In thisRange
localData(currentRow - 1, cellIndex) = cell
cellIndex = cellIndex + 1
Next cell
End If
currentRow = currentRow + 1
Next row
'update list box
ListBox1.ColumnCount = numberOfColumns
ListBox1.Clear
ListBox1.List() = localData
End Sub