Format LastRow style

[Dave Ashcroft]

Hi I have received loads of help from the forum for which I am eternally grateful, I now have yet another question.

The code listed below enters new data into my workbook on the sheet selected by the ComboBox1.Value, this all works great. I now require to format the last row before the the sheet does the sort by column “B”. I have recorded a macro of the desired formats indicated below cmdNew but am at a loss of how to assign it to the last row, has anyone any ideas.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Private Sub cmdNew_Click()
With ThisWorkbook.Worksheets(Me.ComboBox1.Value)
LastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'check for Name
If Trim(Me.txtName.Value) = "" Then
Me.txtName.SetFocus
MsgBox "Please enter Employee Name"
Exit Sub
End If
'check for Badge Number
If Trim(Me.txtBadge.Value) = "" Then
Me.txtBadge.SetFocus
MsgBox "Please enter Employee Badge#"
Exit Sub
End If
'check for Grade Code
If Trim(Me.txtGC.Value) = "" Then
Me.txtGC.SetFocus
MsgBox "Please enter Employee Grade Code"
Exit Sub
End If
'check for Months in IDP
If Trim(Me.txtMon.Value) = "" Then
Me.txtMon.SetFocus
MsgBox "Please enter Number of Months Must be greater than 1"
Exit Sub
End If
'check for Months in IDP
If Trim(Me.txtTas.Value) = "" Then
Me.txtTas.SetFocus
MsgBox "Please enter Number of Tasks Requested This Month"
Exit Sub
End If
'check for Months in IDP
If Trim(Me.txtEva.Value) = "" Then
Me.txtEva.SetFocus
MsgBox "Please enter Number of Tasks Evaluated This Month"
Exit Sub
End If
'check for Months in IDP
If Trim(Me.txtTot.Value) = "" Then
Me.txtTot.SetFocus
MsgBox "Please enter Total Tasks Completed"
Exit Sub
End If
'check for Months in IDP
If Trim(Me.txtJob.Value) = "" Then
Me.txtJob.SetFocus
MsgBox "Please enter Employees Job"
Exit Sub
End If
'copy the data to the database
.Cells(LastRow, 1).Value = Me.txtName.Value
.Cells(LastRow, 2).Value = Me.txtBadge.Value
.Cells(LastRow, 3).Value = Me.txtGC.Value
.Cells(LastRow, 4).Value = Me.txtMon.Value
.Cells(LastRow, 6).Value = Me.txtTas.Value
.Cells(LastRow, 7).Value = Me.txtEva.Value
.Cells(LastRow, 11).Value = Me.txtTot.Value
.Cells(LastRow, 9).Value = Me.txtJob.Value
End With
'clear the data
Me.txtName.Value = ""
Me.txtBadge.Value = ""
Me.txtGC.Value = ""
Me.txtMon.Value = ""
Me.txtTas.Value = ""
Me.txtEva.Value = ""
Me.txtTot.Value = ""
Me.txtJob.Value = ""
Me.txtBadge.SetFocus
New Macro to “format”<o></o>
Range("A4:L50").Select
ActiveWorkbook.Worksheets(Me.ComboBox1.Value).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(Me.ComboBox1.Value).Sort.SortFields.Add Key:=Range( _
"B4:B50"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(Me.ComboBox1.Value).Sort
.SetRange Range("A4:L50")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
<o> </o>
End Sub
<o> </o>
Private Sub Format()
Range("A15:L15").Select
With Selection.Font
.Name = "Arial"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 12611584
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A15").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B15:L15").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

 

[tweedle]

Here's the call:

    Dim rng As Range
    Set rng = Range(Cells(lastrow, 1), Cells(lastrow, 12))
    FormatRow rng

And then something like this, I believe...

Sub FormatRow(rng As Range)
With rng
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlCenter
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
End With
With rng.Font
    .Bold = True
    .Name = "Arial"
    .Size = 9
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With
With rng.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With rng.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With rng.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With rng.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With rng.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With rng.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With rng.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 12611584
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
Set subrng = Cells(rng.Row, 1)  'Cell in Col A
With subrng
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Set subrng = Range(Cells(rng.Row, 2), Cells(rng.Row, 12)) 'Cells in Col B to L
With subrng
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
End Sub

 

[Dave Ashcroft]

Thanks for the quick response, I'm still learning and have been stumped by this for days now.
The call and the format code work great but unfortunately the format appears on the active worksheet rather than the sheet defined by Me.ComboBox1.value.

 

[tweedle]

Ok...

we can change the call to be more specific

    Dim ws As Worksheet
    Dim rng As Range
    Set ws = Sheets(Me.ComboBox1.Value)
    Set rng = ws.Range(ws.Cells(lastrow, 1), ws.Cells(lastrow, 12))
    FormatRow rng

 

[Dave Ashcroft]

Thanks so much for your work that worked perfectly.