I'm creating my first vba user form and im trying to populate a listbox with data, i have it working however if the correct tab isn't open then the data is not added to the list box.
Option Explicit
' Display All Matches from Search in Userform ListBox
'
Dim FormEvents As Boolean
Private Sub ClearForm(Except As String)
' Clears the list box and text boxes EXCEPT the text box
' currently having data entered into it
Select Case Except
Case "txtType"
FormEvents = False
txtStoresCode.Value = ""
txtDesc.Value = ""
txtEquipment.Value = ""
Results.Clear
FormEvents = True
Case "txtStoresCode"
FormEvents = False
txtType.Value = ""
txtDesc.Value = ""
txtEquipment.Value = ""
Results.Clear
FormEvents = True
Case "txtDesc"
FormEvents = False
txtType.Value = ""
txtStoresCode.Value = ""
txtEquipment.Value = ""
Results.Clear
FormEvents = True
Case "txtEquipment"
FormEvents = False
txtType.Value = ""
txtStoresCode.Value = ""
txtDesc.Value = ""
Results.Clear
FormEvents = True
Case Else
FormEvents = False
txtType.Value = ""
txtStoresCode.Value = ""
txtDesc.Value = ""
txtEquipment.Value = ""
Results.Clear
FormEvents = True
End Select
End Sub
Private Sub ClearBtn_Click()
ClearForm ("")
End Sub
Private Sub CloseBtn_Click()
Unload Me
End Sub
Private Sub txtType_Change()
If FormEvents Then ClearForm ("txtType")
End Sub
Private Sub txtStoresCode_Change()
If FormEvents Then ClearForm ("txtStoresCode")
End Sub
Private Sub txtDesc_Change()
If FormEvents Then ClearForm ("txtDesc")
End Sub
Private Sub txtEquipment_Change()
If FormEvents Then ClearForm ("txtEquipment")
End Sub
Private Sub SearchBtn_Click()
Dim SearchTerm As String
Dim SearchColumn As String
Dim RecordRange As Range
Dim FirstAddress As String
Dim FirstCell As Range
Dim RowCount As Integer
' Display an error if no search term is entered
If txtType.Value = "" And txtStoresCode.Value = "" And txtDesc.Value = "" And txtEquipment.Value = "" Then
MsgBox "No search term specified", vbCritical + vbOKOnly
Exit Sub
End If
' Work out what is being searched for
If txtType.Value <> "" Then
SearchTerm = txtType.Value
SearchColumn = "Type"
End If
If txtStoresCode.Value <> "" Then
SearchTerm = txtStoresCode.Value
SearchColumn = "StoresCode"
End If
If txtDesc.Value <> "" Then
SearchTerm = txtDesc.Value
SearchColumn = "Description"
End If
If txtEquipment.Value <> "" Then
SearchTerm = txtEquipment.Value
SearchColumn = "Equipment"
End If
Results.Clear
' Only search in the relevant table column i.e. if somone is searching Location
' only search in the Location column
With Range("Table1[" & SearchColumn & "]")
' Find the first match
Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)
' If a match has been found
If Not RecordRange Is Nothing Then
FirstAddress = RecordRange.Address
RowCount = 0
Do
' Set the first cell in the row of the matching value
Set FirstCell = Range("A" & RecordRange.Row)
Results.AddItem
Results.List(RowCount, 0) = FirstCell(1, 1).Value
Results.List(RowCount, 1) = FirstCell(1, 3).Value
Results.List(RowCount, 2) = FirstCell(1, 4).Value
Results.List(RowCount, 3) = FirstCell(1, 5).Value
Results.List(RowCount, 4) = FirstCell(1, 6).Value
Results.List(RowCount, 5) = FirstCell(1, 7).Value
Results.List(RowCount, 6) = FirstCell(1, 8).Value
Results.List(RowCount, 7) = FirstCell(1, 9).Value
RowCount = RowCount + 1
' Look for next match
Set RecordRange = .FindNext(RecordRange)
' When no further matches are found, exit the sub
If RecordRange Is Nothing Then
Exit Sub
End If
' Keep looking while unique matches are found
Loop While RecordRange.Address <> FirstAddress
Else
' If you get here, no matches were found
Results.AddItem
Results.List(RowCount, 0) = "Nothing Found"
End If
End With
End Sub
Private Sub UserForm_Initialize()
FormEvents = True
I've tied everything that i am able to find online.
I'm hoping that someone will be able to point me in the correct direction.
