How to add an icon to Excel toobar to choose colour of a border

[av]

Hi,
Back again.

Having now created a pre-formatted workbook and worksheet in the
way that I was helped here, in this ng, earlier, I am now setting
up my customized toolbar, with the icons that most suit me.

I have placed on my custom toolbar an icon that lets me choose,
quickly, what kind of border I want to add to a cell, however I
don't know how to place in the same toolbar an icon that will
allow me to choose the *colour* of that border.

I have read in the Excel "Help" notes where it says that the
correct icon is the one by which one chooses the colour of a
drawn line. That icon shows what looks like a paintbrush above a
line in whichever colour was last selected. However, I have
found that when I click on that icon, intending to set a new
colour (or change an existing colour) of a border, *no* colour is
available. The entire palette is greyed out. It is only after I
draw a line and select that line, that the line-colour palette
becomes available to me.

I am using Excel '97. I know that it may not be a big deal, but
I would like this feature added to my customized toolbar if
possible.

Can anyone help me with this please?

With thanks,

avril

 

[Robert Rosenberg]

There is no custom icon that lets you apply border color.

Later versions of Excel have such an icon but it works with a "draw border"
feature. The icon doesn't let you apply border color without drawing the
border first.

The icon you speak of has to do with line color for floating shapes (drawing
objects).

The quickest way to get to the border color is using a macro that
specifically shows the border tab of the Format-->Cells command...

Sub BorderColor()
Application.Dialogs(xlDialogBorder).Show
End Sub

 

[Bernie Deitrick]

avril,

For me, at least, I can only set border colors through a macro, not
through the border formatting dialog (which would make sense).
Anyway, you can create a button and assign a macro to it (below),
though you will need to learn the color indices:

Sub ChangeBorderColor()
Dim myBrd As Border
Dim myColIndex As Integer

myColIndex = Application.InputBox("What Color Index?")

For Each myBrd In Selection.Borders
myBrd.ColorIndex = myColIndex
Next
End Sub

HTH,
Bernie
MS Excel MVP

 

[av]

There is no custom icon that lets you apply border color.

Later versions of Excel have such an icon but it works with a "draw border"
feature. The icon doesn't let you apply border color without drawing the
border first.

The icon you speak of has to do with line color for floating shapes (drawing
objects).

The quickest way to get to the border color is using a macro that
specifically shows the border tab of the Format-->Cells command...

Sub BorderColor()
Application.Dialogs(xlDialogBorder).Show
End Sub


--
_______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

Thank you Robert.
avril

 

[av]

avril,

For me, at least, I can only set border colors through a macro, not
through the border formatting dialog (which would make sense).
Anyway, you can create a button and assign a macro to it (below),
though you will need to learn the color indices:

Sub ChangeBorderColor()
Dim myBrd As Border
Dim myColIndex As Integer

myColIndex = Application.InputBox("What Color Index?")

For Each myBrd In Selection.Borders
myBrd.ColorIndex = myColIndex
Next
End Sub

HTH,
Bernie
MS Excel MVP

Thank you very much Bernie. I will try it also.
All the best,
avril

 

[Dave Peterson]

Another option.

Find the Borders Icon on the Formatting toolbar. Click on that dropdown arrow.

You'll see an option for "draw borders..."
click on that and you'll see a floating toolbar where you can change the color
while you're drawing borders.

In fact, Debra Dalgleish taught me that you can actually "ripoff" that "draw
borders..." toolbar and it'll float over the worksheet.

Just click on the dropdown arrow. You'll see a little border at the top of
that. Click and drag it off the toolbar.

Each might save you some mousing.