Formating values from userform to worksheet

[ipbr21054]

Morning,

I have the code in use below but need some help / advice please.

The code works fine & how i wanted it,then i need to format the vales when i sent them from userform to worksheet.
So because of this i added the extra code shown in RED

It does work but this is where i need some help / advice.

1,
So far i can only work out how to apply it to a range, as opposed to the row at which the values are inserted to the worksheet, current ive set its range to N24:R24

2,
I need to also add to this code All Borders but cant seem to work that part out.

Other than that i think im sorted.

Please advise & have a nice day thanks.

Private Sub CommandButton1_Click()
    Dim i As Long, x As Long
    Dim LastRow As Long
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("G INCOME")
    
    With Sheets("G INCOME")

        If TextBox1.Value = "" Then
            MsgBox "NO CUSTOMER'S NAME WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox1.SetFocus
            Exit Sub
        ElseIf TextBox2.Value = "" Then
            MsgBox "NO ADDRESS WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox2.SetFocus
            Exit Sub
        ElseIf TextBox3.Value = "" Then
            MsgBox "NO POST CODE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox3.SetFocus
            Exit Sub
        ElseIf TextBox4.Value = "" Then
            MsgBox "NO CHARGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox4.SetFocus
            Exit Sub
        ElseIf TextBox5.Value = "" Then
            TextBox5.SetFocus
            MsgBox "NO MILEAGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            Exit Sub
        End If
            
        With ThisWorkbook.Worksheets("G INCOME")
        LastRow = .Cells(.Rows.Count, "N").End(xlUp).Row
        
        With sh.Range("N24:R24")
        .Font.Name = "Calibri"
        .Font.Size = 16
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        
        End With

        .Cells(LastRow + 1, 14).Value = TextBox1.Text
        .Cells(LastRow + 1, 15).Value = TextBox2.Text
        .Cells(LastRow + 1, 16).Value = TextBox3.Text
        .Cells(LastRow + 1, 17).Value = TextBox4.Text
        .Cells(LastRow + 1, 18).Value = TextBox5.Text
        
        
        If .AutoFilterMode Then .AutoFilterMode = False
            x = .Cells(.Rows.Count, 4).End(xlUp).Row
            .Range("N4:R" & x).Sort Key1:=Range("N4"), Order1:=xlAscending, Header:=xlGuess
        
        Unload AddCustomer
        MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
        End With
        Range("N4").Select
        End With
    End Sub

 

[AlphaFrog]

Private Sub CommandButton1_Click()
    Dim i As Long, x As Long
    Dim LastRow   As Long
    
    If TextBox1.Value = "" Then
        MsgBox "NO CUSTOMER'S NAME WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
        TextBox1.SetFocus
        Exit Sub
    ElseIf TextBox2.Value = "" Then
        MsgBox "NO ADDRESS WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
        TextBox2.SetFocus
        Exit Sub
    ElseIf TextBox3.Value = "" Then
        MsgBox "NO POST CODE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
        TextBox3.SetFocus
        Exit Sub
    ElseIf TextBox4.Value = "" Then
        MsgBox "NO CHARGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
        TextBox4.SetFocus
        Exit Sub
    ElseIf TextBox5.Value = "" Then
        TextBox5.SetFocus
        MsgBox "NO MILEAGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
        Exit Sub
    End If
    
    With Sheets("G INCOME")
        
        LastRow = .Cells(.Rows.Count, "N").End(xlUp).Row
        
        With .Range("N" & LastRow + 1 & ":R" & LastRow + 1)
            .Font.Name = "Calibri"
            .Font.Size = 16
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            With .Borders
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
        End With
        
        .Cells(LastRow + 1, 14).Value = TextBox1.Text
        .Cells(LastRow + 1, 15).Value = TextBox2.Text
        .Cells(LastRow + 1, 16).Value = TextBox3.Text
        .Cells(LastRow + 1, 17).Value = TextBox4.Text
        .Cells(LastRow + 1, 18).Value = TextBox5.Text
        
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 4).End(xlUp).Row
        .Range("N4:R" & x).Sort Key1:=Range("N4"), Order1:=xlAscending, Header:=xlGuess
        
        Unload AddCustomer
        MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
        
        .Select
        .Range("N4").Select
    End With
End Sub

 

[dmt32]

Hi,
see if this update to your code does what you want

Private Sub CommandButton1_Click()
    Dim i                   As Integer
    Dim Prompt              As String
    Dim LastRow             As Long
    Dim sh                  As Worksheet
    Dim Data(1 To 5)        As Variant
    Const BordersColorIndex As Long = 3

    
    For i = 1 To 5
        With Me.Controls("TextBox" & i)
            If Len(.Value) = 0 Then
                Prompt = Choose(i, "NO CUSTOMER'S NAME WAS ENTERED", "NO ADDRESS WAS ENTERED", _
                                    "NO POST CODE WAS ENTERED", "NO CHARGE WAS ENTERED", "NO MILEAGE WAS ENTERED")
                MsgBox Prompt, vbCritical, "NAME & ADDRESS Empty MESSAGE"
                .SetFocus
                Exit Sub
            End If
            Data(i) = .Value
        End With
        
    Next i
    
    Set sh = ThisWorkbook.Sheets("G INCOME")
    
    With sh
        
        LastRow = .Cells(.Rows.Count, "N").End(xlUp).Row + 1
        
        With .Cells(LastRow, 14).Resize(, 5)
            .Font.Name = "Calibri"
            .Font.Size = 16
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        'inside
            .Borders.ColorIndex = BordersColorIndex
            .Borders.Weight = xlThin
        'outside
            .BorderAround Weight:=xlThin, ColorIndex:=BordersColorIndex
            .Value = Data
        End With
        
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 4).End(xlUp).Row
        .Range("N4:R" & x).Sort Key1:=Range("N4"), Order1:=xlAscending, Header:=xlGuess
        
        Unload Me
        MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
    End With
    
End Sub

Change the Borders ColorIndex value shown in BOLD as required

Dave

 

[kanadaaa]

If you mean you need to change the format of the cells into which values are inserted....

Private Sub CommandButton1_Click()
    Dim i As Long, x As Long, LastRow As Long
   
    With Sheets("G INCOME")

        If TextBox1.Value = "" Then
            MsgBox "NO CUSTOMER'S NAME WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox1.SetFocus
            Exit Sub
        ElseIf TextBox2.Value = "" Then
            MsgBox "NO ADDRESS WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox2.SetFocus
            Exit Sub
        ElseIf TextBox3.Value = "" Then
            MsgBox "NO POST CODE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox3.SetFocus
            Exit Sub
        ElseIf TextBox4.Value = "" Then
            MsgBox "NO CHARGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox4.SetFocus
            Exit Sub
        ElseIf TextBox5.Value = "" Then
            TextBox5.SetFocus
            MsgBox "NO MILEAGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            Exit Sub
        End If

        LastRow = .Cells(Rows.Count, "N").End(xlUp).Row + 1
       
        Application.ScreenUpdating = False
        For i = 1 To 5
            With .Cells(LastRow, i + 13)
                .Value = Me.Controls("TextBox" & i).Value
                .Font.Name = "Calibri"
                .Font.Size = 16
                .Font.Bold = True
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Borders.Weight = xlThin
            End With
        Next i
        Application.ScreenUpdating = True
       
        If .AutoFilterMode Then .AutoFilterMode = False
            x = .Cells(Rows.Count, 4).End(xlUp).Row
            .Range("N4:R" & x).Sort Key1:=.Range("N4"), Order1:=xlAscending, Header:=xlGuess
       
        Unload AddCustomer
        MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"

        .Range("N4").Select
       
    End With
End Sub

 

[ipbr21054]

Many thanks,ive used post #4

Have a nice day

 

[ipbr21054]

For my learning curve please explain why the figure below is 13 if the first cell which is column N is 14 ?

For i = 1 To 5
            With .Cells(LastRow, i + 13)

 

[kanadaaa]

For my learning curve please explain why the figure below is 13 if the first cell which is column N is 14 ?

For i = 1 To 5
            With .Cells(LastRow, i + 13)

It's "i+13".
There's a For loop while i is 1 to 5, so "i+13" will be 14, 15, 16, 17, and 18.

A little off the topic but that part wrapped in Application.ScreenUpdating can be revised to:

        Application.ScreenUpdating = False
        For i = 1 To 5
            .Cells(LastRow, i + 13).Value = Me.Controls("TextBox" & i).Value
        Next i
        With .Cells(LastRow, 14).Resize(,5)
            .Font.Name = "Calibri"
            .Font.Size = 16
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Borders.Weight = xlThin
        End With
        Application.ScreenUpdating = True

It may be undetectable but presumably this is faster.

 

[ipbr21054]

Thanks very much