Textbox in User-form displaying three digits (first digit is a 0)

R

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
173
Office Version
  1. 365
Platform
  1. Windows
  2. 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: 
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
 
Sort by date Sort by votes
I don't know in which line you want the format.
Just follow the example and put the format in the desired textbox.


Code: 
T_12.Text = [COLOR=#0000ff][B]Format([/B][/COLOR]LB_01.Column(7)[COLOR=#0000ff][B], "000")[/B][/COLOR]
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
You must log in or register to reply here.

Similar threads

K
  • Solved
Change ComboBox list values depending on the selected OptionButton
Replies
2
Views
674
Kakaska
K
I
  • Solved
Userform to worksheet values followed by 3 digits
Replies
2
Views
178
ipbr21054
I
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
308
pure vito
P
M
  • Question
Multiple users using same user form having errors and slow at times
Replies
2
Views
210
Joe4
Joe4
R
  • Solved
Using VBA to achieve looping a user input form and consolidate the output into another worksheet
Replies
1
Views
477
Artik
Artik

Forum statistics

Threads
1,214,574
Messages
6,120,329
Members
448,956
Latest member
Adamsxl

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top