Border in different sheet

[Cyril Beki]

How to apply border to different sheet after clicking on command button (active control) from different sheet? Here is the code i used

Private Sub CommandButton1_Click()
With Sheets("GraphReport")
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("E4", Cells(LastRow, LastCol))
    .BorderAround xlDouble
    .Rows.Borders(xlInsideHorizontal).LineStyle = xlDash
    .Rows.Borders(xlInsideVertical).LineStyle = xlContinuous
    .Columns.AutoFit
End With
End With
End Sub

The command button (active control) is in Sheet7(Dashboard) but i wish to apply the border to Sheet("GraphReport") once i clicked on the button from sheet7.
Although the code do the job but the problem is it will create border in Sheet7(Dashboard) as well, i only wanted it to be created in Sheet("GraphReport") once i clicked the command button. Thanks in advance

 

[Fluff]

You need to put a period infront of all the ranges & cells inside the With statement, otherwise it's looking at the active sheet.

Private Sub CommandButton1_Click()
With Sheets("GraphReport")
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("E4", .Cells(LastRow, LastCol))
    .BorderAround xlDouble
    .Rows.Borders(xlInsideHorizontal).LineStyle = xlDash
    .Rows.Borders(xlInsideVertical).LineStyle = xlContinuous
    .Columns.AutoFit
End With
End With
End Sub

 

[Cyril Beki]

You need to put a period infront of all the ranges & cells inside the With statement, otherwise it's looking at the active sheet.

Private Sub CommandButton1_Click()
With Sheets("GraphReport")
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("E4", .Cells(LastRow, LastCol))
    .BorderAround xlDouble
    .Rows.Borders(xlInsideHorizontal).LineStyle = xlDash
    .Rows.Borders(xlInsideVertical).LineStyle = xlContinuous
    .Columns.AutoFit
End With
End With
End Sub

Thank you , it works

 

[Fluff]

You're welcome & thanks for the feedback.