If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Ad-In Rowliner
How do I prevent the background from changing when I use Row-Liner in a protected worksheet. I used the following VB Code, but the backgrounds I used changed to white.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="union" If Not OldCell Is Nothing Then With OldCell.EntireRow ..Interior.ColorIndex = xlColorIndexNone ..Borders.LineStyle = xlLineStyleNone End With End If Set OldCell = Target With OldCell.EntireRow ..Interior.ColorIndex = 6 ..EntireRow.Borders.LineStyle = xlContinuous End With Else If OldCell Is Nothing Then Set OldCell = Target Else Set OldCell = Union(OldCell, Target) End If End If ActiveSheet.Protect Password:="union" End Sub Submitted via EggHeadCafe - Software Developer Portal of Choice Using VSTO Add-In To Automate Frequent Excel 2007 Tasks http://www.eggheadcafe.com/tutorials...n-to-auto.aspx |
#2
|
|||
|
|||
Ad-In Rowliner
That is not Chip's RowLiner add-in.
It is event code to be used in place of the Add-in because the Add-in won't work on protected sheets. One disadvantage of the code you are using is the wiping out of existing background colors. Gord Dibben MS Excel MVP On Wed, 14 Apr 2010 03:48:20 -0700, Eurika Stemmet wrote: How do I prevent the background from changing when I use Row-Liner in a protected worksheet. I used the following VB Code, but the backgrounds I used changed to white. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="union" If Not OldCell Is Nothing Then With OldCell.EntireRow .Interior.ColorIndex = xlColorIndexNone .Borders.LineStyle = xlLineStyleNone End With End If Set OldCell = Target With OldCell.EntireRow .Interior.ColorIndex = 6 .EntireRow.Borders.LineStyle = xlContinuous End With Else If OldCell Is Nothing Then Set OldCell = Target Else Set OldCell = Union(OldCell, Target) End If End If ActiveSheet.Protect Password:="union" End Sub Submitted via EggHeadCafe - Software Developer Portal of Choice Using VSTO Add-In To Automate Frequent Excel 2007 Tasks http://www.eggheadcafe.com/tutorials...n-to-auto.aspx |
#3
|
|||
|
|||
Highlight active row
How can I highlight the active row in a protected excel worksheet?
Gord Dibben wrote: That is not Chip's RowLiner add-in. 14-Apr-10 That is not Chip's RowLiner add-in. It is event code to be used in place of the Add-in because the Add-in will not work on protected sheets. One disadvantage of the code you are using is the wiping out of existing background colors. Gord Dibben MS Excel MVP Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice WPF GridView Sample To Insert, Update, and Delete Records http://www.eggheadcafe.com/tutorials...ple-to-in.aspx |
#4
|
|||
|
|||
Ad-In Rowliner
This event code from Mike H will not wipe out existing formatting.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'row highlighter 'Mike H.........does not destroy existing formats Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) .Interior.ColorIndex = 20 End With End With End Sub Gord On Wed, 14 Apr 2010 17:25:14 -0700, Gord Dibben gorddibbATshawDOTca wrote: That is not Chip's RowLiner add-in. It is event code to be used in place of the Add-in because the Add-in won't work on protected sheets. One disadvantage of the code you are using is the wiping out of existing background colors. Gord Dibben MS Excel MVP On Wed, 14 Apr 2010 03:48:20 -0700, Eurika Stemmet wrote: How do I prevent the background from changing when I use Row-Liner in a protected worksheet. I used the following VB Code, but the backgrounds I used changed to white. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="union" If Not OldCell Is Nothing Then With OldCell.EntireRow .Interior.ColorIndex = xlColorIndexNone .Borders.LineStyle = xlLineStyleNone End With End If Set OldCell = Target With OldCell.EntireRow .Interior.ColorIndex = 6 .EntireRow.Borders.LineStyle = xlContinuous End With Else If OldCell Is Nothing Then Set OldCell = Target Else Set OldCell = Union(OldCell, Target) End If End If ActiveSheet.Protect Password:="union" End Sub Submitted via EggHeadCafe - Software Developer Portal of Choice Using VSTO Add-In To Automate Frequent Excel 2007 Tasks http://www.eggheadcafe.com/tutorials...n-to-auto.aspx |
#5
|
|||
|
|||
Highlight active row
Go back to your first post.
I added a reply with code a few minutes ago. Gord On Thu, 15 Apr 2010 06:00:28 -0700, Eurika Stemmet wrote: How can I highlight the active row in a protected excel worksheet? Gord Dibben wrote: That is not Chip's RowLiner add-in. 14-Apr-10 That is not Chip's RowLiner add-in. It is event code to be used in place of the Add-in because the Add-in will not work on protected sheets. One disadvantage of the code you are using is the wiping out of existing background colors. Gord Dibben MS Excel MVP Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice WPF GridView Sample To Insert, Update, and Delete Records http://www.eggheadcafe.com/tutorials...ple-to-in.aspx |
Thread Tools | |
Display Modes | |
|
|