Excel - Border around page (not cells) 2

[lenebene]

I was wondering if there was a way to create a border around a page that does not involve using the cells...

I hope this makes sense.

Thanks!

 

[xlhelp]

Simple answer, no. No such thing.

Having said that.......

you could embed an Excel object in a word doc and have a border around that. Depending on the size of your data, it can look mighty ugly.

Member AAA - Abolish Abused Abbreviations

 

[lenebene]

Yea that was the only way I had figured it out also... Thank you!

 

[SkipVought]

Hi,

Yet another less than desireable solution.

View>Page Break Preview.

NOW...

seeing where the "pages" will break, format with border.

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[Zathras]

Another kludge: Make your own stationery that has nothing but the border you want. Then print your output using those prepared sheets.

 

[xlhelp]

Hi, this will put a thick border around your data. (It also removes any borders you currently have.)

Option Explicit
Sub border()
Dim wkSheet As Worksheet
Dim x As Long, cSht As Long, rng As Range


For Each wkSheet In Application.ActiveWorkbook. _
Windows(1).SelectedSheets

'-- Clear all borders
Cells.Borders(xlDiagonalDown).LineStyle = xlNone
Cells.Borders(xlDiagonalUp).LineStyle = xlNone
Cells.Borders(xlEdgeLeft).LineStyle = xlNone
Cells.Borders(xlEdgeTop).LineStyle = xlNone
Cells.Borders(xlEdgeBottom).LineStyle = xlNone
Cells.Borders(xlEdgeRight).LineStyle = xlNone
Cells.Borders(xlInsideVertical).LineStyle = xlNone
Cells.Borders(xlInsideHorizontal).LineStyle = xlNone


'-- put a thick border around usedrange

Set rng = wkSheet.UsedRange
With rng.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
End With

With rng.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
End With

With rng.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
End With

With rng.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
End With

Next wkSheet

If Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count > 1 Then
MsgBox "Please UNGROUP selected sheets"
End If

End Sub

Member AAA - Abolish Abused Abbreviations

 

[DaleWatson123321]

lenebene,

Here's yet another option - use "Copy Picture". This will be especially easy if you're only dealing with 1 page of data.

After creating a "picture", you can then right-click on it, and use "Format Picture" - from which you can choose from a variety of different type of lines, colours, etc.

"Copy Picture" is not obvious. (For some strange reason, Microsoft continues to "hide" this option.)
It only becomes visible when you:
a) hold down <Shift>, and
b) click "Edit" on Excel's menu.

The pictures remain linked to your original data; so any change(s) you make to your data will automatically be reflected in your picture.

Usually, the best practice is to paste your picture on a SEPARATE sheet - where you'll have flexibility to position it, format it, re-shape it (if desired, you can stretch or shrink the picture to suit your needs).

Be aware - highlight your range FIRST - before you activate "Copy Picture".

Be aware that the picture will include gridlines, so if you don't want the gridlines included, you'll need to remove them (Tools - Options - View, and de-select Gridlines).

For those who wish to have the "Camera" option more handy, you could add it to one of your existing toolbars, and activate it with <Alt> C. It's simple to set this up, as follows:
a) Right-click on the toolbar area, and choose "Customize".
b) Click the "Commands" tab
c) Under "Categories", click "Tools"
d) Under "Commands", scroll down until you reach "Camera".
e) Click-and-drag the "Camera" icon to a toolbar.
f) Right-click on the icon, and choose "Text Only (Always)".

I hope you find this useful.

For some additional info, I have an FAQ - faq68-1161

Regards, Dale Watson

 

[xlhelp]

Hey, Dale.

Haven't seen your name pop-up in a dog's age. Good to see it.

Member AAA - Abolish Abused Abbreviations

 

[SkipVought]

A suprise to see you, Dale,

but no supprise at the depth and detail presented in your excellent post.

==> *

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[DaleWatson123321]

Ahhhhhhh geeeeee - thanks guys, 'tis much appreciated!!!

Dale

 

[GlennUK]

Dale, good to see you back again, and another excellent post.

===> *

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.