Automatically put borders around Data

[Cyril Beki]

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

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

 

[maabadi]

Do you use this code at worksheet Change event?
If Not Right Click on sheet name, Select View Code , then Paste this 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

 

[Cyril Beki]

Do you use this code at worksheet Change event?
If Not Right Click on sheet name, Select View Code , then Paste this 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)

 

[maabadi]

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.

 

[Cyril Beki]

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

 

[maabadi]

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

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

 

[Cyril Beki]

After Select view Code on command button, you should see this code at VBA window

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.

 

[My Aswer Is This]

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

 

[Cyril Beki]

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

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

 

[maabadi]

Then you should put Borders code at the end of your code before End Sub