Robert_Conklin
Board Regular
- Joined
- Jun 19, 2017
- Messages
- 173
- Office Version
- 365
- Platform
- Windows
- MacOS
Is it possible to get a text box in my user-form to display a three digit number when the first digit is 0? The text box name is T07.
Code:
Code:
Code:
Option ExplicitDim lSearchCol As Long, lLastRow As Long, l As Long, lFound As Long
Dim SearchRange As Range, FoundCells As Range, FoundCell As Range
Dim FindWhat As Variant, arrResults() As Variant
Dim strAddress As String, strCellComment As String
Dim Ctrl As Control
Private Sub CMB_clear_Click()
ListBox_Results.ListIndex = -1
ListBox_Results.Clear
For Each Ctrl In Controls
If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
Next Ctrl
TextBox_Find.SetFocus
End Sub
Private Sub CMB_clear2_Click()
ListBox_Results2.ListIndex = -1
ListBox_Results2.Clear
For Each Ctrl In Controls
If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
Next Ctrl
TextBox_Find2.SetFocus
End Sub
Private Sub CMB_close_Click()
Unload Me
End Sub
Private Sub F_01_Click()
End Sub
Private Sub F_02_Click()
End Sub
Private Sub Label5_Click()
End Sub
Private Sub TextBox_Find_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Call FindAllMatches
End Sub
Private Sub TextBox_Find2_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Call FindAllMatches2
End Sub
Sub FindAllMatches()
If Len(TextBox_Find.Value) > 1 Then
Set SearchRange = Sheets("Raw Data").Range("Pnames").Cells
FindWhat = TextBox_Find.Value
Set FoundCells = FindAll(SearchRange:=SearchRange, _
FindWhat:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
BeginsWith:=vbNullString, _
EndsWith:=vbNullString, _
BeginEndCompare:=vbTextCompare)
If FoundCells Is Nothing Then
ReDim arrResults(1 To 1, 1 To 2)
arrResults(1, 1) = "No Results"
Else
ReDim arrResults(1 To FoundCells.Count, 1 To 6)
lFound = 1
For Each FoundCell In FoundCells
arrResults(lFound, 1) = FoundCell.Offset(, -1).Value
arrResults(lFound, 2) = FoundCell.Address
arrResults(lFound, 3) = FoundCell.Value
arrResults(lFound, 4) = FoundCell.Offset(, 1).Value
arrResults(lFound, 5) = FoundCell.Offset(, 2).Value
arrResults(lFound, 6) = FoundCell.Offset(, 3).Value
lFound = lFound + 1
Next FoundCell
End If
ListBox_Results.List = arrResults
Else
ListBox_Results.Clear
End If
End Sub
Sub FindAllMatches2()
If Len(TextBox_Find2.Value) > 1 Then
Set SearchRange = Sheets("After Hours Contact Numbers").Range("AHPnames").Cells
FindWhat = TextBox_Find2.Value
Set FoundCells = FindAll(SearchRange:=SearchRange, _
FindWhat:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
BeginsWith:=vbNullString, _
EndsWith:=vbNullString, _
BeginEndCompare:=vbTextCompare)
If FoundCells Is Nothing Then
ReDim arrResults(1 To 1, 1 To 2)
arrResults(1, 1) = "No Results"
Else
ReDim arrResults(1 To FoundCells.Count, 1 To 5)
lFound = 1
For Each FoundCell In FoundCells
arrResults(lFound, 1) = FoundCell.Value
arrResults(lFound, 2) = FoundCell.Address
arrResults(lFound, 3) = FoundCell.Offset(, 1).Value
arrResults(lFound, 4) = FoundCell.Offset(, 2).Value
arrResults(lFound, 5) = FoundCell.Offset(, 3).Value
lFound = lFound + 1
Next FoundCell
End If
ListBox_Results2.List = arrResults
Else
ListBox_Results2.Clear
End If
End Sub
Private Sub ListBox_Results_Click()
For l = 0 To ListBox_Results.ListCount
If ListBox_Results.Selected(l) = True Then
strAddress = ListBox_Results.List(l, 1)
With Sheets("Raw Data")
T01.Value = .Cells(.Range(strAddress).Row, 1).Value
T02.Value = .Cells(.Range(strAddress).Row, 2).Value
T03.Value = .Cells(.Range(strAddress).Row, 3).Value
T04.Value = .Cells(.Range(strAddress).Row, 4).Value
T05.Value = .Cells(.Range(strAddress).Row, 5).Value
T06.Value = .Cells(.Range(strAddress).Row, 6).Value
T07.Value = .Cells(.Range(strAddress).Row, 7).Value
T08.Value = .Cells(.Range(strAddress).Row, 8).Value
T09.Value = .Cells(.Range(strAddress).Row, 9).Value
T10.Value = .Cells(.Range(strAddress).Row, 10).Value
T11.Value = .Cells(.Range(strAddress).Row, 11).Value
T12.Value = .Cells(.Range(strAddress).Row, 12).Value
T13.Value = .Cells(.Range(strAddress).Row, 13).Value
T14.Value = .Cells(.Range(strAddress).Row, 14).Value
T15.Value = .Cells(.Range(strAddress).Row, 15).Value
T16.Value = .Cells(.Range(strAddress).Row, 16).Value
T17.Value = .Cells(.Range(strAddress).Row, 17).Value
T18.Value = .Cells(.Range(strAddress).Row, 18).Value
T19.Value = .Cells(.Range(strAddress).Row, 19).Value
T20.Value = .Cells(.Range(strAddress).Row, 20).Value
T21.Value = .Cells(.Range(strAddress).Row, 21).Value
T22.Value = .Cells(.Range(strAddress).Row, 22).Value
T23.Value = .Cells(.Range(strAddress).Row, 23).Value
T24.Value = .Cells(.Range(strAddress).Row, 24).Value
T25.Value = .Cells(.Range(strAddress).Row, 25).Value
T26.Value = .Cells(.Range(strAddress).Row, 26).Value
T27.Value = .Cells(.Range(strAddress).Row, 27).Value
T28.Value = .Cells(.Range(strAddress).Row, 28).Value
T29.Value = .Cells(.Range(strAddress).Row, 29).Value
T30.Value = .Cells(.Range(strAddress).Row, 30).Value
T31.Value = .Cells(.Range(strAddress).Row, 31).Value
T32.Value = .Cells(.Range(strAddress).Row, 32).Value
T33.Value = .Cells(.Range(strAddress).Row, 33).Value
T34.Value = .Cells(.Range(strAddress).Row, 34).Value
T35.Value = .Cells(.Range(strAddress).Row, 35).Value
T36.Value = .Cells(.Range(strAddress).Row, 36).Value
T37.Value = .Cells(.Range(strAddress).Row, 37).Value
T38.Value = .Cells(.Range(strAddress).Row, 38).Value
T39.Value = .Cells(.Range(strAddress).Row, 39).Value
T40.Value = .Cells(.Range(strAddress).Row, 40).Value
T41.Value = .Cells(.Range(strAddress).Row, 41).Value
T42.Value = .Cells(.Range(strAddress).Row, 42).Value
T43.Value = .Cells(.Range(strAddress).Row, 43).Value
T44.Value = .Cells(.Range(strAddress).Row, 44).Value
T45.Value = .Cells(.Range(strAddress).Row, 45).Value
T46.Value = .Cells(.Range(strAddress).Row, 46).Value
T47.Value = .Cells(.Range(strAddress).Row, 47).Value
T48.Value = .Cells(.Range(strAddress).Row, 48).Value
T49.Value = .Cells(.Range(strAddress).Row, 49).Value
T50.Value = .Cells(.Range(strAddress).Row, 50).Value
End With
GoTo EndLoop
End If
Next l
EndLoop:
End Sub
Private Sub ListBox_Results2_Click()
For l = 0 To ListBox_Results2.ListCount
If ListBox_Results2.Selected(l) = True Then
strAddress = ListBox_Results2.List(l, 1)
With Sheets("After Hours Contact Numbers")
T2_info.Text = ""
T2_01.Value = .Cells(.Range(strAddress).Row, 2).Value
T2_02.Value = .Cells(.Range(strAddress).Row, 1).Value
T2_03.Value = .Cells(.Range(strAddress).Row, 3).Value
T2_04.Value = .Cells(.Range(strAddress).Row, 4).Value
T2_05.Value = .Cells(.Range(strAddress).Row, 5).Value
T2_06.Value = .Cells(.Range(strAddress).Row, 6).Value
T2_07.Value = .Cells(.Range(strAddress).Row, 7).Value
T2_08.Value = .Cells(.Range(strAddress).Row, 8).Value
T2_09.Value = .Cells(.Range(strAddress).Row, 9).Value
T2_10.Value = .Cells(.Range(strAddress).Row, 10).Value
T2_11.Value = .Cells(.Range(strAddress).Row, 11).Value
On Error Resume Next
T2_info.Text = .Cells(.Range(strAddress).Row, 1).Comment.Text
End With
GoTo EndLoop
End If
Next l
EndLoop:
End Sub