Checkbox to toggle "Private Sub workbook_SheetSelectionChange" function

[Viktor86HUN]

Hello.

I've just recently got a solved issue on this forum.
But it has generated me another issue.

Just got a code applied to the workbook which highlights rows.
What i would like to have the option to turn the feature off with a checkbox on the main sheet. (the code applies to the whole workbook, it would be okay to change the code, to only apply to the main sheet aswell)

[COLOR=#0000cd]Private Sub workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  Application.ScreenUpdating = False
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = RGB(255, 230, 153)
  Application.ReplaceFormat.Interior.Color = xlNone
  Cells.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = xlNone
  Application.ReplaceFormat.Interior.Color = RGB(255, 230, 153)
  Target.EntireRow.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.ScreenUpdating = True
End Sub[/COLOR]

Possible upcoming bug:
When i'll have a row highlighted, and turn off the feature, than the highlighting would not disappear.

Any help would be appretiated

 

[Viktor86HUN]

Here is the updated code which applies only the the main workbook(which i would prefer to use):

[COLOR=#006400]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = False
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = RGB(255, 230, 153)
  Application.ReplaceFormat.Interior.Color = xlNone
  Cells.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = xlNone
  Application.ReplaceFormat.Interior.Color = RGB(255, 230, 153)
  Target.EntireRow.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.ScreenUpdating = True
End Sub[/COLOR]

 

[Rick Rothstein]

Hello.

I've just recently got a solved issue on this forum.
But it has generated me another issue.

Just got a code applied to the workbook which highlights rows.
What i would like to have the option to turn the feature off with a checkbox on the main sheet. (the code applies to the whole workbook, it would be okay to change the code, to only apply to the main sheet aswell)

[COLOR=#0000cd]Private Sub workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  [B][COLOR="#FF0000"]If Sheet2.Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then[/COLOR][/B]
    Application.ScreenUpdating = False
    Application.FindFormat.Clear
    Application.ReplaceFormat.Clear
    Application.FindFormat.Interior.Color = RGB(255, 230, 153)
    Application.ReplaceFormat.Interior.Color = xlNone
    Cells.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
    Application.FindFormat.Clear
    Application.ReplaceFormat.Clear
    Application.FindFormat.Interior.Color = xlNone
    Application.ReplaceFormat.Interior.Color = RGB(255, 230, 153)
    Target.EntireRow.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
    Application.FindFormat.Clear
    Application.ReplaceFormat.Clear
    Application.ScreenUpdating = True
  [B][COLOR="#FF0000"]End If[/COLOR][/B]End Sub[/COLOR]

Possible upcoming bug:
When i'll have a row highlighted, and turn off the feature, than the highlighting would not disappear.

Any help would be appretiated

First off, I am assuming you are adding a Forms control checkbox whose name is "Check Box 1" (change this throughout if your checkbox ends up with a different name). Given that, make the change I show in red above. Second, add a general Module (Insert/Module from the VB editor's menu bar) and put this code in it...

Sub ClearYellowColor()
  If Sheet2.Shapes("Check Box 2").OLEFormat.Object.Value <> 1 Then
    Application.ScreenUpdating = False
    Application.FindFormat.Clear
    Application.ReplaceFormat.Clear
    Application.FindFormat.Interior.Color = vbYellow
    Application.ReplaceFormat.Interior.Color = xlNone
    Cells.EntireRow.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
    Application.FindFormat.Clear
    Application.ReplaceFormat.Clear
    Application.ScreenUpdating = True
  End If
End Sub

Next, right click the checkbox and select "Assign Macro" from the popup menu that appears and then select "ClearYellowColor" from the list and click OK. Everything should now work as you want... if the checkbox is checked, you get yellow highlighted rows, if it is not checked, the highlighting is disabled and the existing yellow color is removed.

 

[Viktor86HUN]

Doing the victory dance...
Thanks for all your efforts helping me out Rick.

It was a good day...already