Formating values from userform to worksheet

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
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.



Rich (BB code):
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
Hi,
see if this update to your code does what you want

Rich (BB code):
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
 
Last edited:

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
345
Office Version
  1. 365
Platform
  1. Windows
If you mean you need to change the format of the cells into which values are inserted....
VBA Code:
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
 
Last edited:
Solution

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Many thanks,ive used post #4

Have a nice day
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
For my learning curve please explain why the figure below is 13 if the first cell which is column N is 14 ?


Rich (BB code):
For i = 1 To 5
            With .Cells(LastRow, i + 13)
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
345
Office Version
  1. 365
Platform
  1. Windows
For my learning curve please explain why the figure below is 13 if the first cell which is column N is 14 ?


Rich (BB code):
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:
VBA Code:
        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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,157
Members
417,011
Latest member
Amaden95

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
Top