VBA code to add a border

J

Javi

Active Member
Joined
May 26, 2011
Messages
440
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"


Code: 
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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.

Code: 
[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]
 
Upvote 0
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.
 
Upvote 0
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
'
'
Code: 
 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
 
Upvote 0
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
Code: 
    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

Code: 
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.

Code: 
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
Code: 
'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
 
Upvote 0
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!!


Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
VBA DOUBT- can we add heading color and remove blank columns in this code?
Replies
0
Views
168
Kishore1982
K
M
  • Question Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
415
mikefisher
M
M
  • Question Question
Add extra line of code to VBA to copy cell value across sheet
Replies
1
Views
70
myall_blues
myall_blues
M
  • Solved
Adding extra Or value to this VBA code
2
Replies
10
Views
144
MeaclH
M
togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
1K
MARK858
MARK858

Forum statistics

Threads
1,224,586
Messages
6,179,723
Members
452,939
Latest member
WCrawford

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