Automatically put borders around Data

Cyril Beki

Board Regular
Joined
Sep 18, 2021
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
Hello Expert,

I've read previous post regarding border (Automatically put borders around my data using VBA),
And i used code provided by Rick Rothsein to try to solve my issue in my Userform VBA but the code will not add the border to the new data automatiacally once data is filled.
Which part of code should be changed in order to make the border automatically filled in as soon as data is added ?
My data Column is starting from A8 to Q8. I want the border to be automatically filled as soon as data is added. Thank you in advance. Can anyone help me. Code is below

VBA Code:
Dim LastRow As Long, LastCol As Long
  Cells.Borders.LineStyle = xlNone
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  LastCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  With Range("A8", Cells(LastRow, LastCol))
    .BorderAround xlDouble
    .Rows.Borders(xlInsideHorizontal).LineStyle = xlDash
    .Rows.Borders(xlInsideVertical).LineStyle = xlContinuous
    .Columns.AutoFit
  End With
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Do you use this code at worksheet Change event?
If Not Right Click on sheet name, Select View Code , then Paste this code:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRow As Long, LastCol As Long
  Cells.Borders.LineStyle = xlNone
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  LastCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  If Intersect(Target, Range(Cells(8, 1), Cells(LastRow + 1, LastCol + 1))) Is Nothing Then Exit Sub
  With Range("A8", Cells(LastRow, LastCol))
    .BorderAround xlDouble
    .Rows.Borders(xlInsideHorizontal).LineStyle = xlDash
    .Rows.Borders(xlInsideVertical).LineStyle = xlContinuous
    .Columns.AutoFit
  End With
End Sub
 
Upvote 0
Do you use this code at worksheet Change event?
If Not Right Click on sheet name, Select View Code , then Paste this code:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRow As Long, LastCol As Long
  Cells.Borders.LineStyle = xlNone
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  LastCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  If Intersect(Target, Range(Cells(8, 1), Cells(LastRow + 1, LastCol + 1))) Is Nothing Then Exit Sub
  With Range("A8", Cells(LastRow, LastCol))
    .BorderAround xlDouble
    .Rows.Borders(xlInsideHorizontal).LineStyle = xlDash
    .Rows.Borders(xlInsideVertical).LineStyle = xlContinuous
    .Columns.AutoFit
  End With
End Sub
Doesn't Work, I already try paste your code in worksheet, it doesn't show any border although i have data in it
I have command button (Add Button). I want the border to be produced once i clicked on the Add button (including the data).

Edit: i Tried paste your code in Command button (ADD), Still failed (Refer image for error)
 

Attachments

  • Object Required.PNG
    Object Required.PNG
    3.2 KB · Views: 7
Last edited:
Upvote 0
I have command button (Add Button)

if you want to work with Command button , Right click on command button ( First you should enable Design mode at Developer Tab) select view Code Then Paste Rick Rothsein Code there.
 
Upvote 0
if you want to work with Command button , Right click on command button ( First you should enable Design mode at Developer Tab) select view Code Then Paste Rick Rothsein Code there.
But the code doesn't do as i wanted to be, i want the border to be produced as soon as i clicked on the add button
 
Upvote 0
But the code doesn't do as i wanted to be, i want the border to be produced as soon as i clicked on the add button
After Select view Code on command button, you should see this code at VBA window
VBA Code:
Private Sub CommandButton1_Click()
Dim LastRow As Long, LastCol As Long
  Cells.Borders.LineStyle = xlNone
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  LastCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  With Range("A8", Cells(LastRow, LastCol))
    .BorderAround xlDouble
    .Rows.Borders(xlInsideHorizontal).LineStyle = xlDash
    .Rows.Borders(xlInsideVertical).LineStyle = xlContinuous
    .Columns.AutoFit
  End With
End Sub
 
Upvote 0
After Select view Code on command button, you should see this code at VBA window
VBA Code:
Private Sub CommandButton1_Click()
Dim LastRow As Long, LastCol As Long
  Cells.Borders.LineStyle = xlNone
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  LastCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  With Range("A8", Cells(LastRow, LastCol))
    .BorderAround xlDouble
    .Rows.Borders(xlInsideHorizontal).LineStyle = xlDash
    .Rows.Borders(xlInsideVertical).LineStyle = xlContinuous
    .Columns.AutoFit
  End With
End Sub
The border won't produced on the latest data, (Refer Image). The border only cover the previous data.
 

Attachments

  • Border.PNG
    Border.PNG
    1.1 KB · Views: 9
Upvote 0
I've been following this thread myself. I assume your saying you have some code already in your button. But now want to add borders also.
Show us the code your using to enter your data without borders. When you said automatically I also assumed you did not plan to press a button. I assumed you wanted the borders and your data at the same time without the need of clicking a button
 
Upvote 0
I've been following this thread myself. I assume your saying you have some code already in your button. But now want to add borders also.
Show us the code your using to enter your data without borders. When you said automatically I also assumed you did not plan to press a button. I assumed you wanted the borders and your data at the same time without the need of clicking a button
Yes, You got the point, i wanted the border and data at the same time. It doesn't matter click button or not, as long as it produced border the same time with data. below is the code for Add button
VBA Code:
Dim text As Object
Set text = Sheet6.Range("A5000").End(xlUp)

If txtDown1.Value = "" Then
MsgBox "Fill in the Downtime", vbCritical
Exit Sub
End If

If txtUp1.Value = "" Then
MsgBox "Fill in the Uptime", vbCritical
Exit Sub
End If

Select Case MsgBox("You will saved the recent data" _
& vbCrLf & "Are you sure?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Adding Data")
Case vbNo
Exit Sub
Case vbYes
End Select

'Numbering
Me.txtNo.Value = "=Row()-1"

'Adding command
text.Offset(1, 0).Value = Me.txtNo.Value
text.Offset(1, 1).Value = Me.txtSection.Value
text.Offset(1, 2).Value = Me.txtDate.Value

'Day/Night
If Me.txtDay.Value = True Then
text.Offset(1, 3).Value = "Day"
End If
If Me.txtNight.Value = True Then
text.Offset(1, 3).Value = "Night"
End If

'Shift
If Me.txtA.Value = True Then
text.Offset(1, 4).Value = "A"
End If
If Me.txtB.Value = True Then
text.Offset(1, 4).Value = "B"
End If
If Me.txtC.Value = True Then
text.Offset(1, 4).Value = "C"
End If

'Machine
text.Offset(1, 5).Value = Me.txtMachine.Value

'Category
text.Offset(1, 6).Value = Me.txtCategory.Value

'Tube/Paddle/Side
text.Offset(1, 7).Value = Me.txtTube.Value

'Alarm Message
text.Offset(1, 8).Value = Me.txtAlarm.Value

'Problem
text.Offset(1, 9).Value = Me.txtProblem.Value

'Action Taken
text.Offset(1, 10).Value = Me.txtAction.Value

'Action By
Dim i As Long
Dim strActionBy As String

strActionBy = ""

For i = 0 To txtActionby.ListCount - 1
    If txtActionby.Selected(i) Then strActionBy = IIf(strActionBy = "", txtActionby.List(i), strActionBy & vbLf & txtActionby.List(i)) ' There is a space after comma for readability
Next i

text.Offset(1, 11).Value = strActionBy

'Machine Status
If Me.txtUp.Value = True Then
text.Offset(1, 12).Value = "Up"
End If
If Me.txtDown.Value = True Then
text.Offset(1, 12).Value = "Down"
End If

'Uptime Downtime
text.Offset(1, 13).Value = Me.txtDown1.Value
text.Offset(1, 14).Value = Me.txtUp1.Value
text.Offset(1, 15).Value = _
Abs(TimeValue(Me.txtUp1) - TimeValue(Me.txtDown1))

'Part Change
text.Offset(1, 16).Value = Me.txtPart1.Value

Sheet6.Select
Me.ListBox1.RowSource = Sheet6.Range("Passdown").Address(External:=True)
MsgBox ("Data is added succesfully")

'Clear form after add
Me.txtNo.Value = ""
Me.txtDate.Value = ""
Me.txtSection.Value = ""
Me.txtMachine.Value = ""
Me.txtCategory.Value = ""
Me.txtTube.Value = ""
Me.txtAlarm.Value = ""
Me.txtProblem.Value = ""
Me.txtAction.Value = ""
Me.txtActionby.Value = ""
Me.txtPart1.Value = ""
Me.txtDay.Value = False
Me.txtNight.Value = False
Me.txtA.Value = False
Me.txtB.Value = False
Me.txtC.Value = False
Me.txtUp.Value = False
Me.txtDown.Value = False
Me.txtDown1.Value = ""
Me.txtUp1.Value = ""
End Sub
 
Upvote 0
Then you should put Borders code at the end of your code before End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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