Add Cell border for active cells

Help Studio
, , , , ,
1

How to add cell Borders, only in active cells before convert excel into pdf file as shown in below

ExcelBorder
ExcelBorder1620Ă—648 37.3 KB

PdfBorder
PdfBorder1198Ă—519 28 KB

Can anyone help me!?

2

Hi @jai_kumar2

Install BalaReva Easy Excel Activities → manage package

Have a look the video

Regards
Gokul

1 Like
3

Hello @jai_kumar2
Try this method
1. Use excel Application Scope
2. Drag and drop the send hot key
3. SET ctrl+A
4.Another Send hot key activity
5.Set “Shift+Alt+H+B+A”

you can get results

4

hi @Gokul_Jayakumar
thanks for your reply

But this is not dynamic way
if you see the picture ,there only active cells are highlighted with border lines

5

@jai_kumar2 Got it. Then you have to look the video and set boder for each set of tables seperately.

6

If you have Excel installed and can execute VBA, you can use this macro in an Invoke VBA activity. It will work dynamically, just pass it your sheet name as a parameter.

Save this script in your project directory, for example in a text file named vba.txt.

Function ApplyCellBorderActiveCells(sheetName As String) As String
    On Error GoTo ErrorHandler
    For Each cell In Sheets(sheetName).UsedRange
        If cell.Text = vbNullString Then GoTo SkipCell
        With cell.Borders
            .LineStyle = xlContinuous
            .Color = xlBlack
        End With
SkipCell:
    Next cell
    ApplyCellBorderActiveCells = "Success"
    Exit Function
ErrorHandler:
    Debug.Print Err.Description
    ApplyCellBorderActiveCells = "Error: " & Err.Description
End Function

Example usage:

image
image1322Ă—561 18.9 KB

Result:
image

You may need to enable access to VBA if this is your first time doing it. If so,

  1. Open Excel and go into File > Options
  2. Click the Trust Centre tab
  3. Click Trust Centre Settings…
  4. Click the Macro Settings tab
  5. Check the box for Trust access to the VBA project object model
  6. Click OK
1 Like
7

Hi Moosh,

Its Working

Thanks for the solution

8

Hi moosh

can we add one or more sheets names as a parameter?

can you please help me on this

9

Sure, amend your script to this:

Function ApplyCellBorderActiveCells(ParamArray shts() As Variant) As String
    On Error GoTo ErrorHandler
    For Each sht In shts
        For Each cell In sheets(sht).UsedRange
            If cell.Text = vbNullString Then GoTo SkipCell
            With cell.Borders
                .LineStyle = xlContinuous
                .Color = xlBlack
            End With
SkipCell:
        Next cell
    Next sht
    ApplyCellBorderActiveCells = "Success"
    Exit Function
ErrorHandler:
    Debug.Print Err.Description
    ApplyCellBorderActiveCells = "Error: " & Err.Description
End Function

And pass in multiple sheets like so:
image

10

Hi moosh,

Thanks for your reply

but still some merged cells are left blank

Do i need to change some thing in script to make it on merged cells also

11

Yup, use this one for merged cells too

Function ApplyCellBorderActiveCells(ParamArray shts() As Variant) As String
    On Error GoTo ErrorHandler
    Dim rng, borderRng As Range
    For Each sht In shts
        For Each cell In Sheets(sht).UsedRange
            If cell.Text = vbNullString Then GoTo SkipCell
            If cell.MergeCells Then
                Set rng = cell.MergeArea
            Else
                Set rng = cell
            End If
            If borderRng Is Nothing Then
                Set borderRng = rng
            Else
                Set borderRng = Union(borderRng, rng)
            End If
SkipCell:
        Next cell
        With borderRng.Cells
            .Borders.LineStyle = xlContinuous
            .Borders.Color = xlBlack
        End With
        Set borderRng = Nothing
    Next sht
    ApplyCellBorderActiveCells = "Success"
    Exit Function
ErrorHandler:
    Debug.Print Err.Description
    ApplyCellBorderActiveCells = "Error: " & Err.Description
End Function
1 Like
12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.