PDA

View Full Version : Solved: KB Article



austenr
08-25-2010, 09:32 AM
Wanting to use Ken P's KB code and was wondering where i can find all of the possible xl...... possibilities.

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

Bob Phillips
08-25-2010, 09:46 AM
Look in the object browser.

austenr
08-25-2010, 10:01 AM
Came up with some code. It errors on the line in red:

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

Shred Dude
08-25-2010, 10:19 AM
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
08-25-2010, 10:29 AM
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
08-25-2010, 10:37 AM
thanks Mark.

mdmackillop
08-25-2010, 11:10 AM
Two options

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

austenr
08-25-2010, 12:24 PM
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
08-25-2010, 01:22 PM
Create a style with no border and white font then set it as

Selection.Style = "hidden"

austenr
08-25-2010, 01:27 PM
Not sure I follow you Malcomb?

mdmackillop
08-25-2010, 02:11 PM
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
08-25-2010, 02:15 PM
Thanks I didn't know that

austenr
08-25-2010, 02:21 PM
ok created a style called "HideCells"

Used this code:

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


getting an error on the .selection.style line.

mdmackillop
08-25-2010, 02:25 PM
Still selecting after all this time?
Try


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

GTO
08-25-2010, 03:19 PM
Two options

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



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

austenr
08-25-2010, 05:21 PM
Hi Malcomb,

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

mdmackillop
08-25-2010, 11:22 PM
Range("F10").Style = "Normal"

austenr
08-26-2010, 05:59 AM
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
08-26-2010, 12:16 PM
This will toggle between style and normal on coded cells

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

austenr
08-26-2010, 12:23 PM
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.

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

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

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

mdmackillop
08-26-2010, 01:07 PM
That looks fine. Functions are not required.

austenr
08-26-2010, 01:31 PM
Thanks for the help Malcomb.