Private Sub CmbKeyworker_Change()
Dim X, y(), i As Long, ii As Integer, iii As Integer
X = Sheets("Calls").Cells(1).CurrentRegion
For i = 1 To UBound(X, 1)
If X(i, 1) = CmbKeyworker Then
ii = ii + 1: ReDim Preserve y(1 To 7, 1 To ii)
For iii = 1 To 7
y(iii, ii) = X(i, iii)
Next
End If
Next
If Not IsEmpty(y) Then
ListBox1.List = Application.Transpose(y)
Else
MsgBox "No Incidents with a " & CmbKeyworker & " Status."
End If
With Me.ListBox1
For i = 0 To .ListCount - 1
.List(i, 3) = Format(.List(i, 3), "hh:mm")
Next i
End With
' ListBox1.Clear
'Dim rngToSearch As Range
' Dim rngToFind As Range
' Dim valToFind As Variant
' Dim arrClearList()
'
' valToFind = CmbKeyworker.Value 'Edit ComboBox1 to your ComboBox name
'
' With Worksheets("Calls")
' Set rngToSearch = .Columns("A")
' End With
'
' Set rngToFind = rngToSearch.Find(What:=valToFind, _
' LookIn:=xlFormulas, _
' LookAt:=xlWhole, _
' SearchOrder:=xlByRows, _
' SearchDirection:=xlNext, _
' MatchCase:=False)
'
' If Not rngToFind Is Nothing Then
'
' 'Call ClearList(Me.ListBox1) 'Optional to clear existing list
'
' ListBox1.AddItem
'
' With ListBox1
' .List(.ListCount - 1, 0) = rngToFind.Value 'ID Col A
' .List(.ListCount - 1, 1) = rngToFind.Offset(0, 1).Value 'Agent Name Col B
' .List(.ListCount - 1, 2) = rngToFind.Offset(0, 2).Value 'Address Col D
' .List(.ListCount - 1, 3) = rngToFind.Offset(0, 4).Value 'Organization Col E
' .List(.ListCount - 1, 4) = rngToFind.Offset(0, 5).Value 'Phone Col F
' .List(.ListCount - 1, 5) = rngToFind.Offset(0, 6).Value 'Email col G
' End With
'
' Else
' MsgBox valToFind & " not found in worksheet."
' End If
End Sub
Private Sub CmbKeyworker1_Change()
Dim X, y(), i As Long, ii As Integer, iii As Integer
X = Sheets("Calls").Cells(1).CurrentRegion
For i = 1 To UBound(X, 1)
If X(i, 1) = CmbKeyworker1 Then
ii = ii + 1: ReDim Preserve y(1 To 9, 1 To ii)
For iii = 1 To 9
y(iii, ii) = X(i, iii)
Next
End If
Next
If Not IsEmpty(y) Then
ListBox1.List = Application.Transpose(y)
Else
MsgBox "No Incidents with a " & CmbKeyworker1 & " Status."
End If
With Me.ListBox1
For i = 0 To .ListCount - 1
.List(i, 3) = Format(.List(i, 3), "hh:mm")
Next i
End With
End Sub
Private Sub CmdAddData_Click()
Call Module1.Data
End Sub
Private Sub ListBox1_Click()
Dim rngToSearch As Range
Dim rngToFind As Range
Dim valToFind As Variant
valToFind = ListBox1.Value 'Edit ListBox1 to your ListBox name
With Worksheets("Calls")
Set rngToSearch = .Columns("A")
End With
Set rngToFind = rngToSearch.Find(What:=valToFind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngToFind Is Nothing Then
'The GoTo command is a one line operation to
'Select a worksheet and then select a range
Application.Goto rngToFind
Else
MsgBox valToFind & " not found in worksheet."
End If
End Sub
Private Sub UserForm_Initialize()
CmbKeyworker.Clear
CmbPatient.Clear
' ComboBox1.List = Application.WorksheetFunction.Transpose(Range("Keyworker"))
CmbKeyworker.RowSource = "Keyworker"
CmbKeyworker1.RowSource = "Keyworker"
'Dim LstRw As Long, Rw As Long
' With Sheets("Calls")
' LstRw = .Cells(Rows.Count, "A").End(xlUp).Row
' For Rw = 2 To LstRw
' Me.ComboBox1.AddItem (.Cells(Rw, "A"))
' Next Rw
' End With
'Dim LstRw1 As Long, Rw1 As Long
' With Sheets("Calls")
' LstRw1 = .Cells(Rows.Count, "B").End(xlUp).Row
' For Rw1 = 2 To LstRw1
' Me.ComboBox2.AddItem (.Cells(Rw1, "B"))
' Next Rw1
' End With
' Range("A2", Range("A" & Rows.Count).End(xlUp)).Name = "Dynamic"
' Me.ComboBox1.RowSource = "Dynamic"
CmbPatient.RowSource = "Patient"
TxtName.Value = Application.UserName
TxtDate.Value = Now
TxtDate.Value = Format(Date, ddmmyy)
TxtTime.Value = Now
TxtTime.Value = Format(Time, "hh:mm")
CmbType.AddItem "Tasked"
CmbType.AddItem "Emailed"
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
UserForm2.Show
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
TxtName.Value = Application.UserName
TxtConfirm.Value = "Yes"
End Sub
Private Sub CmdEnter_Click()
Dim DBS As Range
Dim IND, X As Integer
With UserForm1.ListBox1
.Column(7) = Me.TxtName
.Column(8) = Me.TxtConfirm
Set DBS = Sheets("Calls").Range("A10000").End(xlUp)
For X = 8 To 9
IND = .ListIndex + 2
Sheet1.Cells(IND, X) = .Column(X - 1)
'DBS.Offset(1, X - 1) = .Column(X - 1)
Next X
End With