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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Hi,
Try something like this

Rich (BB code): 
T07.Value = Format(.Cells(.Range(strAddress).Row, 7).Value, "000")
 
Last edited:
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
Same question, different workbook. I am trying to place code for the same issue into my Private Sub UserForm_Initialize() but cannot figure out how to write it.

Code: 
Private Sub UserForm_Initialize()'Textbox and Combobox values
    T_id.Value = WorksheetFunction.Max([ids]) + 1
    LB_01.List = [database].Value
    C_01.List = [Action].Value
    C_02.List = [datalist].Value
    C_03.List = [nouns].Value
    C_04.List = [mrp].Value
    C_05.List = [BUoM].Value
    C_06.List = [matgroup].Value
    C_07.List = [bom].Value
    T_01.Value = Now
    T_01.Locked = True
    T_23.Value = Environ("Username")
    T_23.Locked = True
End Sub

Any advice would be greatly appreciated.
 
Upvote 0
Robert_Conklin said:
Same question, different workbook. I am trying to place code for the same issue into my Private Sub UserForm_Initialize() but cannot figure out how to write it.

Code: 
Private Sub UserForm_Initialize()'Textbox and Combobox values
    T_id.Value = WorksheetFunction.Max([ids]) + 1
    LB_01.List = [database].Value
    C_01.List = [Action].Value
    C_02.List = [datalist].Value
    C_03.List = [nouns].Value
    C_04.List = [mrp].Value
    C_05.List = [BUoM].Value
    C_06.List = [matgroup].Value
    C_07.List = [bom].Value
    T_01.Value = Now
    T_01.Locked = True
    T_23.Value = Environ("Username")
    T_23.Locked = True
End Sub

Any advice would be greatly appreciated.
Click to expand...

In which textbox do you have the problem?
 
Upvote 0
Robert_Conklin said:
Ooops, I forgot to mention it. The text box name is T_12
Click to expand...


T_12, It is not in the post code #6 , But try this:

Code: 
T12.Value = [COLOR=#0000ff]Format[/COLOR](.Cells(.Range(strAddress).Row, 12).Value, [COLOR=#0000ff]"000"[/COLOR])
 
Upvote 0
I tried the code, but it generated an error message that stated that strAddress was not defined. Here is my complete code so you will have the whole picture. I might be trying to place the code in the wrong spot.

Code: 
Option ExplicitDim rng As Range, fnd As Range, ApproverList As Range
Dim Ctrl As Control
Dim iRow As Long, i As Long
Dim wsAE As Worksheet
Dim ApproveName As String
Dim m As Variant
Dim tbx As OLEObject
Dim cell
Private Sub C_01_Change()
    If C_01.Value <> "" Then C_01.BackColor = RGB(255, 255, 255)
End Sub
Private Sub C_02_Change()
    If C_02.Value <> "" Then C_02.BackColor = RGB(255, 255, 255)
End Sub
Private Sub C_02_Click()
'Autofill Plant Number, Profit Center, and Purchasing Group
    T_02.Text = C_02.Column(1)
    T_12.Text = C_02.Column(3)
    T_13.Value = C_02.Column(4)
    T_35.Text = C_02.Column(9)
End Sub
Private Sub C_03_Change()
    If C_03.Value <> "" Then C_03.BackColor = RGB(255, 255, 255)
End Sub
Private Sub C_04_Change()
    If C_04.Value <> "" Then C_04.BackColor = RGB(255, 255, 255)
    If C_04.Value = "VB" Then
        T_14.BackColor = RGB(255, 138, 138)
        T_15.BackColor = RGB(255, 138, 138)
    Else
        T_14.BackColor = RGB(255, 255, 255)
        T_15.BackColor = RGB(255, 255, 255)
    End If
End Sub
Private Sub C_04_Click()
'Autofill LOT SIZE
    If C_04.Value = "VB" Then T_11.Value = "HB"
    If C_04.Value <> "VB" Then T_11.Value = ""
End Sub
Private Sub C_05_Change()
    If C_05.Value <> "" Then C_05.BackColor = RGB(255, 255, 255)
End Sub
Private Sub C_06_Change()
    If C_06.Value <> "" Then C_06.BackColor = RGB(255, 255, 255)
End Sub
Private Sub C_07_Change()
    If C_07.Value <> "" Then C_07.BackColor = RGB(255, 255, 255)
End Sub
Private Sub CMB_AddNew_Click()
'Code for Submit Button
    Set wsAE = Worksheets("ADD-EXTEND")
    
    Sheets("ADD-EXTEND").Columns("C").NumberFormat = "@"
    Sheets("ADD-EXTEND").Columns("G").NumberFormat = "@"
    
    T_10.Value = C_03.Value & ";" & T_06.Value & ":" & T_05.Value & "," & T_07.Value & "," & T_09.Value
    If C_01.Value = "" Then
        C_01.BackColor = RGB(255, 138, 138)
        MsgBox "The field [Indicate Action] is a mandatory field!", vbCritical, "Check!"
        C_01.SetFocus
        Exit Sub
    End If
    If C_01.Value = "ADD" And C_02.Value <> "" And C_03.Value <> "" And _
    T_07.Value <> "" And C_04.Value <> "" And C_05.Value <> "" And C_06.Value <> "" And _
    C_06.Value <> "" And T_16.Value <> "" And C_07.Value <> "" Then
        GoTo continue
    Else
        If C_01.Value = "ADD" Then
            If C_02.Value = "" Then C_02.BackColor = RGB(255, 138, 138)
            If C_03.Value = "" Then C_03.BackColor = RGB(255, 138, 138)
            If T_07.Value = "" Then T_07.BackColor = RGB(255, 138, 138)
            If C_04.Value = "" Then C_04.BackColor = RGB(255, 138, 138)
            If C_05.Value = "" Then C_05.BackColor = RGB(255, 138, 138)
            If C_06.Value = "" Then C_06.BackColor = RGB(255, 138, 138)
            If C_04.Value = "VB" And T_14 = "" Then T_14.BackColor = RGB(255, 138, 138)
            If C_04.Value = "VB" And T_15 = "" Then T_15.BackColor = RGB(255, 138, 138)
            If T_16.Value = "" Then T_16.BackColor = RGB(255, 138, 138)
            If C_07.Value = "" Then C_07.BackColor = RGB(255, 138, 138)
            MsgBox "Please Fill In ALL Required Fields", vbCritical, "Check!"
            Exit Sub
        End If
    End If
    If C_01.Value = "EXTEND" And C_02.Value <> "" And T_04.Value <> "" And _
    T_08.Value <> "" And C_04.Value <> "" And T_16.Value <> "" And C_07.Value <> "" Then
        GoTo continue


    Else
        If C_01.Value = "EXTEND" Then
            If C_02.Value = "" Then C_02.BackColor = RGB(255, 138, 138)
            If T_04.Value = "" Then T_04.BackColor = RGB(255, 138, 138)
            If T_08.Value = "" Then T_08.BackColor = RGB(255, 138, 138)
            If C_04.Value = "" Then C_04.BackColor = RGB(255, 138, 138)
            If T_16.Value = "" Then T_16.BackColor = RGB(255, 138, 138)
            If C_07.Value = "" Then C_07.BackColor = RGB(255, 138, 138)
            If C_04.Value = "VB" And T_14 = "" Then T_14.BackColor = RGB(255, 138, 138)
            If C_04.Value = "VB" And T_15 = "" Then T_15.BackColor = RGB(255, 138, 138)
            MsgBox "Please Fill In ALL Required Fields", vbCritical, "Check!"
            Exit Sub
        End If
    End If
continue:
    If MsgBox("Correct entry?", vbYesNo + vbQuestion, "Check the data!") = vbNo Then Exit Sub
    iRow = wsAE.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    wsAE.Cells(iRow, 1).Resize(, 38).Value = Array _
        (T_id.Value, C_02.Value, T_02.Text, C_01.Value, T_04.Value, T_35.Value, T_03.Value, T_12.Text, T_13.Value, _
         C_05.Value, C_04.Value, T_11.Value, C_03.Value, T_06.Value, T_05.Value, T_07.Value, T_09.Value, _
         T_10.Value, T_08.Value, T_26.Value, T_14.Value, T_15.Value, T_16.Value, C_06.Value, T_17.Value, _
         T_30.Value, T_31.Value, T_32.Value, T_33.Value, T_34.Value, C_07.Value, T_23.Value, T_01.Value, _
         T_24.Value, T_29.Value, T_28.Value, T_27.Value, T_25.Value)


    'Columns.AutoFit
    MsgBox "The new entry has been saved.", vbInformation, "Done"
    For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
    Next Ctrl
        LB_01.ListIndex = -1
        LB_01.TopIndex = 0
    Call UserForm_Initialize
End Sub


Private Sub CMB_Change_Click()
'Code for Change Button
    T_27 = Now
    T_28 = Environ("Username")
    T_10.Value = C_03.Value & ";" & T_06.Value & ":" & T_05.Value & "," & T_07.Value & "," & T_09.Value


    Set wsAE = Worksheets("ADD-EXTEND")
    Set rng = wsAE.Range("A2:A" & wsAE.Cells(Rows.Count, "A").End(xlUp).Row)
    Set fnd = rng.Find(What:=T_id.Value, LookIn:=xlValues, Lookat:=xlWhole)
    If LB_01.ListIndex = -1 Then
        MsgBox "First choose a item in the list!", vbCritical, "Attention!"
        Exit Sub
    Else
        If T_id = vbNullString Then
            MsgBox "Customizing is not possible, no entries found", vbExclamation, "Attention!"
            Exit Sub
        ElseIf Not fnd Is Nothing Then
            Application.EnableEvents = False
            If MsgBox("Correct entry?", vbYesNo + vbQuestion, "Check the data!") = vbNo Then Exit Sub
            wsAE.Cells(fnd.Row, "A").Resize(, 38).Value = Array _
        (T_id.Value, C_02.Value, T_02.Text, C_01.Value, T_04.Value, T_35.Value, T_03.Value, T_12.Text, T_13.Value, _
         C_05.Value, C_04.Value, T_11.Value, C_03.Value, T_06.Value, T_05.Value, T_07.Value, T_09.Value, _
         T_10.Value, T_08.Value, T_26.Value, T_14.Value, T_15.Value, T_16.Value, C_06.Value, T_17.Value, _
         T_30.Value, T_31.Value, T_32.Value, T_33.Value, T_34.Value, C_07.Value, T_23.Value, T_01.Value, _
         T_24.Value, T_29.Value, T_28.Value, T_27.Value, T_25.Value)
            'Columns.AutoFit
            MsgBox "The changes have been saved.", vbInformation, "Done"
            Application.EnableEvents = True
        End If
        For Each Ctrl In Controls
            If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
        Next Ctrl
            LB_01.ListIndex = -1
            LB_01.TopIndex = 0
        Call UserForm_Initialize
    End If
End Sub
Private Sub CMB_Clear_Click()
'Clear all fields
    For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then
            Ctrl.Value = ""
            Ctrl.BackColor = RGB(255, 255, 255)
        End If
    Next Ctrl
        LB_01.ListIndex = -1
        LB_01.TopIndex = 0
    Call UserForm_Initialize
End Sub
Private Sub CMB_Close_Click()
'Close the userform
    Unload Me
End Sub
Private Sub CMB_PRINT_Click()
'Print function
Application.ScreenUpdating = False
If LB_01.ListIndex = -1 Then
        MsgBox "First choose a item in the list!", vbCritical, "Attention!"
        LB_01.SetFocus
        Exit Sub
    End If
    With Sheets("ADD-EXTEND PRINT")
        Worksheets("ADD-EXTEND PRINT").Visible = True
        .OLEObjects("T_sh01").Object.Text = T_id.Value
        .OLEObjects("T_sh02").Object.Text = C_01.Value
        .OLEObjects("T_sh03").Object.Text = C_02.Value
        .OLEObjects("T_sh04").Object.Text = T_02.Value
        .OLEObjects("T_sh05").Object.Text = T_03.Value
        .OLEObjects("T_sh06").Object.Text = T_04.Value
        .OLEObjects("T_sh07").Object.Text = T_05.Value
        .OLEObjects("T_sh08").Object.Text = C_03.Value
        .OLEObjects("T_sh09").Object.Text = T_06.Value
        .OLEObjects("T_sh10").Object.Text = T_07.Value
        .OLEObjects("T_sh11").Object.Text = T_08.Value
        .OLEObjects("T_sh12").Object.Text = T_09.Value
        .OLEObjects("T_sh13").Object.Text = T_10.Value
        .OLEObjects("T_sh14").Object.Text = C_04.Value
        .OLEObjects("T_sh15").Object.Text = T_11.Value
        .OLEObjects("T_sh16").Object.Text = C_05.Value
        .OLEObjects("T_sh17").Object.Text = C_06.Value
        .OLEObjects("T_sh18").Object.Text = T_12.Value
        .OLEObjects("T_sh19").Object.Text = T_13.Value
        .OLEObjects("T_sh20").Object.Text = T_14.Value
        .OLEObjects("T_sh21").Object.Text = T_15.Value
        .OLEObjects("T_sh22").Object.Text = T_16.Value
        .OLEObjects("T_sh23").Object.Text = C_07.Value
        .OLEObjects("T_sh24").Object.Text = T_17.Value
        .OLEObjects("T_sh33").Object.Text = T_30.Value
        .OLEObjects("T_sh34").Object.Text = T_31.Value
        .OLEObjects("T_sh35").Object.Text = T_32.Value
        .OLEObjects("T_sh36").Object.Text = T_33.Value
        .OLEObjects("T_sh37").Object.Text = T_34.Value
        .OLEObjects("T_sh25").Object.Text = T_26.Value
        .OLEObjects("T_sh26").Object.Text = T_01.Value
        .OLEObjects("T_sh27").Object.Text = T_23.Value
        .OLEObjects("T_sh28").Object.Text = T_29.Value
        .OLEObjects("T_sh29").Object.Text = T_24.Value
        .OLEObjects("T_sh30").Object.Text = T_27.Value
        .OLEObjects("T_sh31").Object.Text = T_28.Value
        .OLEObjects("T_sh32").Object.Text = T_25.Value
        .PrintOut
        End With
        Application.ScreenUpdating = True
         Worksheets("ADD-EXTEND PRINT").Visible = xlHidden
         For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
    Next Ctrl
    For Each tbx In Sheets("ADD-EXTEND PRINT").OLEObjects
        If TypeName(tbx.Object) = "TextBox" Then
            tbx.Object.Text = ""
        End If
    Next
    Call UserForm_Initialize
End Sub


Private Sub LB_01_Click()
'List box column order
    T_id.Value = LB_01.Column(0)
    C_02.Value = LB_01.Column(1)
    T_02.Text = LB_01.Column(2)
    C_01.Value = LB_01.Column(3)
    T_04.Value = LB_01.Column(4)
    T_35.Value = LB_01.Column(5)
    T_03.Value = LB_01.Column(6)
    T_12.Text = LB_01.Column(7)
    T_13.Value = LB_01.Column(8)
    C_05.Value = LB_01.Column(9)
    C_04.Value = LB_01.Column(10)
    T_11.Value = LB_01.Column(11)
    C_03.Value = LB_01.Column(12)
    T_06.Value = LB_01.Column(13)
    T_05.Value = LB_01.Column(14)
    T_07.Value = LB_01.Column(15)
    T_09.Value = LB_01.Column(16)
    T_10.Value = LB_01.Column(17)
    T_08.Value = LB_01.Column(18)
    T_26.Value = LB_01.Column(19)
    T_14.Value = LB_01.Column(20)
    T_15.Value = LB_01.Column(21)
    T_16.Value = LB_01.Column(22)
    C_06.Value = LB_01.Column(23)
    T_17.Value = LB_01.Column(24)
    T_30.Value = LB_01.Column(25)
    T_31.Value = LB_01.Column(26)
    T_32.Value = LB_01.Column(27)
    T_33.Value = LB_01.Column(28)
    T_34.Value = LB_01.Column(29)
    C_07.Value = LB_01.Column(30)
    T_23.Value = LB_01.Column(31)
    T_01.Value = LB_01.Column(32)
    T_24.Value = LB_01.Column(33)
    T_29.Value = LB_01.Column(34)
    T_28.Value = LB_01.Column(35)
    T_27.Value = LB_01.Column(36)
    T_25.Value = LB_01.Column(37)
    End Sub
Private Sub T_03_Change()
    If T_03.Value <> "" Then T_03.BackColor = RGB(255, 255, 255)
End Sub
Private Sub T_04_Change()
    If T_04.Value <> "" Then T_04.BackColor = RGB(255, 255, 255)
End Sub
Private Sub T_05_Change()
    If T_05.Value <> "" Then T_05.BackColor = RGB(255, 255, 255)
End Sub
Private Sub T_06_Change()
    If T_06.Value <> "" Then T_06.BackColor = RGB(255, 255, 255)
End Sub
Private Sub T_07_Change()
    If T_07.Value <> "" Then T_07.BackColor = RGB(255, 255, 255)
End Sub
Private Sub T_08_Change()
    If T_08.Value <> "" Then T_08.BackColor = RGB(255, 255, 255)
End Sub
Private Sub T_14_Change()
    If T_14.Value <> "" Then T_14.BackColor = RGB(255, 255, 255)
End Sub
Private Sub T_15_Change()
    If T_15.Value <> "" Then T_15.BackColor = RGB(255, 255, 255)
End Sub
Private Sub T_16_Change()
    If T_16.Value <> "" Then T_16.BackColor = RGB(255, 255, 255)
End Sub
Private Sub UserForm_Initialize()
'Textbox and Combobox values
    T_id.Value = WorksheetFunction.Max([ids]) + 1
    LB_01.List = [database].Value
    C_01.List = [Action].Value
    C_02.List = [datalist].Value
    C_03.List = [nouns].Value
    C_04.List = [mrp].Value
    C_05.List = [BUoM].Value
    C_06.List = [matgroup].Value
    C_07.List = [bom].Value
    T_01.Value = Now
    T_01.Locked = True
    T_23.Value = Environ("Username")
    T_23.Locked = True
End Sub
Private Sub CMB_Approve_Click()
    T_24.Value = Environ("Username")
    ApproveName = T_24.Value
    T_29.Value = Now
    T_24.Locked = True


    With Worksheets("LISTS")
        Set ApproverList = .Range(.Range("R2"), .Range("R" & .Rows.Count).End(xlUp))
    End With


    m = Application.Match(ApproveName, ApproverList, False)


    If Not IsError(m) Then
        If MsgBox("Hey " & T_24.Value & " are you sure you want to approve all?", vbYesNo + vbQuestion, "Sure!") = vbNo Then Exit Sub
        Set rng = [ids]
        For Each cell In rng
            If cell.Value <> "" Then
                cell.Offset(0, 33).Value = T_24.Value
                cell.Offset(0, 34).Value = Now
            End If
        Next
        MsgBox "Approved!", vbInformation, "Done."
    Else
        MsgBox ApproveName & Chr(10) & "You Do Not Have Authorization To Approve This!", vbCritical, "Attention"
        T_24.Value = ""
    End If
End Sub
 
Upvote 0
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
677
Kakaska
K
I
  • Solved
Userform to worksheet values followed by 3 digits
Replies
2
Views
182
ipbr21054
I
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
315
pure vito
P
M
  • Question
Multiple users using same user form having errors and slow at times
Replies
2
Views
218
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,649
Messages
6,120,733
Members
448,987
Latest member
marion_davis

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