Combine two predefined borders

[sammy]

Hi All,

I'm using MS Office 2007; on the Home tab of the ribbon, on the Font panel,
there is a button to add borders around cells. This button has several
predefined options and i use two alot, i mean really, alot. They are "All
Borders" and "Thick Box Border".

I find that very often i first apply the All Borders and then immediately
apply Thick Box Border. This gives me a thick box around a range of cells
with thin line borders.

Is there a way to define my own border that can give me this result with
only one pick?

Thanks,
Sammy

 

[ShaneDevenshire]

Hi,

You will need to use VBA:

Sub MyBorders()
On Error Resume Next
With Selection
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin
End With
End Sub

You might put this in the personal macro workbook so its available for all
workbooks. You can assign a shortcut key and your set.

 

[sammy]

Hi Shane,

Thanks for the perfect code but as i'm sure you know, it is only available
in the current book. I think my next answer will be found in your suggestion
to put it in a personal macro workbook but how would i run it if it is save
in another workbook?

It seems that the code is available with Alt+F8 as long as the workbook
containing the code is open. Is that the trick?

Thanks,
Sammy

 

[ShaneDevenshire]

Hi,

To create the personal macro workbook, choose Tools, Macro, Record new
Macro, and change the Store Macro In option to Personal Macro Workbook and
click OK.

Stop the macro recorder, move to the Visual Basic Editor and copy the
previous code to the project Personal.xls. Delete the Macro1 that you
started to record. When you close Excel you will be asked if you want to
save the Personal file, respond Yes.

Excel automatically stores this file in the \XLStart folder, and opens it
everytime you start Excel. Excel also hides the sheets for this workbook.
All of this is handled automatically.

Get rid of the old macro from your other workbook. Assign a shortcut key to
the new macro.

 

[sammy]

Hi,

I am using MS Office 2007 so your instructions were just a little off, for
instance, I no longer have a Tools menu. However, they were close enough to
get the job done.

Thanks for your directions, which I used now and stored for future reference.

Sammy