VBA code to add a border

[Javi]

The below code adds a line to the next blank row. I would like to add a border to this row that the values are going into. Columns "A:G"

Private Sub CMD_Add_Click()
Dim rNextCl As Range
 
Set rNextCl = Worksheets("Main").Cells(Rows.Count, 2).End(xlUp).Offset(2, 0)
Worksheets("Main").Activate
rNextCl.Select
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Main")
    'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.Unprotect
'find  first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
  .End(xlUp).Offset(1, 0).Row
'check for a part number
'If Trim(Me.branch.Value) = "" Then
  'Me.branch.SetFocus
  'MsgBox "Please enter a Branch number"
If Me.name = "" Or Me.username = "" Or Me.password = "" Or Me.who = "" Or Me.kind = "" Or Me.site = "" Then
MsgBox ("All Feilds Must be Completed")
  Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 2).Value = Me.name1.Value
ws.Cells(iRow, 3).Value = Me.username.Value
ws.Cells(iRow, 4).Value = Me.password.Value
ws.Cells(iRow, 5).Value = Me.who.Value
ws.Cells(iRow, 6).Value = Me.kind.Value
ws.Cells(iRow, 7).Value = Me.site.Value

'Add one to column "A" for the line number
'ws.Cells(iRow, 1).Value = ws.Cells(iRow - 1, 1).Value
ws.Cells(iRow, 1).Value = ws.Cells(iRow - 1, 1).Value + 1
'clear the data
Me.name1.Value = ""
Me.username.Value = ""
Me.password.Value = ""
Me.who.Value = ""
Me.kind.Value = ""
Me.site.Value = ""
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    'ActiveSheet.Unprotect
End Sub

 

[Javi]

?

 

[mikerickson]

What have you tried?

 

[Javi]

I have not done much if any formating via VBA I'm new to this.

 

[mikerickson]

What does the macro recorder give you when you put a border around the cells?

 

[Javi]

Thank you for taking your time to discuss this with me. That is my typical approach is to start with the recorder however I was not sure how to even attempt to have this formatting apply to only the cells that are referenced in the below code. In other words the range is going to refer to the next available row where the user form will insert the data.

[COLOR=black][FONT=Courier New]'copy the data to the database<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]ws.Cells(iRow, 2).Value = Me.name1.Value<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]ws.Cells(iRow, 3).Value = Me.username.Value<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]ws.Cells(iRow, 4).Value = Me.password.Value<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]ws.Cells(iRow, 5).Value = Me.who.Value<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]ws.Cells(iRow, 6).Value = Me.kind.Value<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]ws.Cells(iRow, 7).Value = Me.site.Value[/FONT][/COLOR]

 

[mikerickson]

My plan is to get you to "border" cells with the Macro Recorder and then show you how to incorporate that code into your existing routine. In addition to the didactic aspects, this will insure that border color, thickness etc meets your needs.

 

[Javi]

Great I'm ready,

The below is the range im looking to border however I only want to border the same row my code is using to enter the data from the user form
.

Sub Border_1()
'
' Border_1 Macro
'
'

 Range("A4:G4").Select
    Range("G4").Activate
    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
    End Sub

 

[mikerickson]

In that code, all the action is taking place relative to Selection. (As is the case with all code from the macro recorder.) To integrate it into your code, you just have to replace Selection with your range.

Your range is columns A:G of row iRow, which, in VBA, is Cells(iRow,1).Resize(1,7).

So you could use code that looks like

    Cells(iRow,1).Resize(1,7).Borders(xlDiagonalDown).LineStyle = xlNone
    Cells(iRow,1).Resize(1,7).Borders(xlDiagonalUp).LineStyle = xlNone
    With Cells(iRow,1).Resize(1,7).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Cells(iRow,1).Resize(1,7).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
'...

or use a With..End With to tidy that up

With Cells(iRow,1).Resize(1,7)
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    With .Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With .Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With .Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
'....
End With

Since you are setting all the borders the same, you can use the whole Borders collection rather than specifying each one.

With Cells(iRow, 1).Resize(7, 1)
    With .Borders
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End With

So the integrated code would look like

'copy the data to the database
With ws
    .Cells(iRow, 2).Value = Me.name1.Value
    .Cells(iRow, 3).Value = Me.username.Value
    .Cells(iRow, 4).Value = Me.password.Value
    .Cells(iRow, 5).Value = Me.who.Value
    .Cells(iRow, 6).Value = Me.kind.Value
    .Cells(iRow, 7).Value = Me.site.Value

    With .Cells(iRow, 1).Resize(7, 1).Borders
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End With

 

[Javi]

It worked perfectly after a small modifications the original code put the borders all in column "A" seven cells down. I made the change and success.

What does the Resize referred to in the code?

I appreciate your approach and help with this code. Thanks You!!

With ws
    .Cells(iRow, 2).Value = Me.name1.Value
    .Cells(iRow, 3).Value = Me.username.Value
    .Cells(iRow, 4).Value = Me.password.Value
    .Cells(iRow, 5).Value = Me.who.Value
    .Cells(iRow, 6).Value = Me.kind.Value
    .Cells(iRow, 7).Value = Me.site.Value

    With .Cells(iRow, 1).Resize(7, 1).Borders
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With