Solved: KB Article

[austenr]

Wanting to use Ken P's KB code and was wondering where i can find all of the possible xl...... possibilities.

[VBA]With Selection
'Remove cell colors
.Interior.ColorIndex = xlNone

'Remove all cell borders
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone

'Remove all special font properties and formatting
With .Font
.FontStyle = "Regular"
.Strikethrough = False
.Superscript = False
.Subscript = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic

End With
End With[/VBA]

[Bob Phillips]

Look in the object browser.

[austenr]

Came up with some code. It errors on the line in red:

[VBA]Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A1:L" & lr)
.BorderAround , xlNone
.borders(xlInsideHorizontal).Weight = xlNone
.borders(xlInsideVertical).Weight = xlNone

End With
'turns font automatic
With Range("A1:L" & lr)
.FontStyle = "Regular"
.Strikethrough = False
.Superscript = False
.Subscript = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2

End With[/VBA]

[Shred Dude]

From the Object Browser it seems the second argument to BorderAround is an xlBorderWeight. xlNone is not within that enumeration. I didn't try it but, that might be the cause of the error.

[GTO]

Howdy,

I don't believe you can use BorderAround to clear it. I'd be happy to be wrong, but I at least do not see a combination of args that works.

Maybe (ignore the range I used):

    With Range("A1:G20")
        .Borders(xlEdgeLeft).LineStyle = xlLineStyleNone
        .Borders(xlEdgeTop).LineStyle = xlLineStyleNone
        .Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
        .Borders(xlEdgeRight).LineStyle = xlLineStyleNone
    End With
End Sub

Hope that helps,

Mark

[austenr]

thanks Mark.

[mdmackillop]

Two options
[VBA]
With Selection
'Remove all cell borders
.Borders.LineStyle = xlNone
'Remove all including diagonals
For i = 5 To 12
.Borders(i).LineStyle = xlNone
Next
End With

[/VBA]

[austenr]

Thanks guys that has given me some options. Now for some opinions. I am attaching a sample WB. On sheet one there are boxes I need to hide depending on if there is data in certain cells. Example:

Range C3:F6. If there is nothing in the cell E5, then I need to make the border invisible, and turn the font white along with the border in cell D5.(my way of saying hide it).

There are 8 other similar ranges on the sheet I need to do. To complicate matters more there are 12 different scenarios that populate these cells.

What is the easiest way to remove the borders and "white" the font? I will need to put them back again to run a different scenario.

[mdmackillop]

Create a style with no border and white font then set it as
[VBA]
Selection.Style = "hidden"

[/VBA]

[austenr]

Not sure I follow you Malcomb?

[mdmackillop]

Format a cell as you want it to appear and give it a style name. You can then apply that style to a cell or group of cells. The style can contain multiple format options. FYI "comma" is a style which contains number formatting as well as some spacing.

[austenr]

Thanks I didn't know that

[austenr]

ok created a style called "HideCells"

Used this code:

[VBA]Sub blah()
Range("F10").Select
With Selection
.Selection.Style = "HideCells"
End With
End Sub
[/VBA]

getting an error on the .selection.style line.

[mdmackillop]

Still selecting after all this time?
Try

[VBA]

Sub blah()
Range("F10").Style = "HideCells"
End Sub

[/VBA]

[GTO]

Two options
[vba]
With Selection
'Remove all cell borders
.Borders.LineStyle = xlNone
'Remove all including diagonals
For i = 5 To 12
.Borders(i).LineStyle = xlNone
Next
End With

[/vba]

(Hi and Bravo!) That's nice Malcom! I would not have thought of the loop in a million years!

[austenr]

Hi Malcomb,

That worked great. Now how do you remove the style?

[mdmackillop]

[VBA]Range("F10").Style = "Normal"
[/VBA]

[austenr]

OK i found that solution after I posted my last comment, however it doesn't put back the formats that were in the cells before I used the style. Borders, Currency, etc. Is there a way around that?

[mdmackillop]

This will toggle between style and normal on coded cells
[VBA]
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim st As String
Cancel = True
Select Case Target.Address(0, 0)
Case "H5", "I5", "J5"
st = "Yellow"
Case "H6", "I6", "J6"
st = "Pink"
End Select
Call ChangeStyle(Target, st)
End Sub

Sub ChangeStyle(Target, Style)
If Target.Style = Style Then
Target.Style = "Normal"
Else
Target.Style = Style
End If
End Sub

[/VBA]

[austenr]

Thanks Malcomb. I was fooling around with it and came up with this. Created two styles. One to take away borders and font, and one to put it back.

[VBA]Sub TakeAway()
Range("F7, F10, F13, H7, H10, H13, K7, K10, K13, I4, I15").Style = "RemoveBordersFont"
End Sub[/VBA]

[VBA]Sub PutBack()
Range("F7, F10, F13, H7, H10, H13, K7, K10, K13, I4, I15").Style = "PutBordersBack"
End Sub[/VBA]

guess ill just call them from inside my code or do they have to be functions?