Results 1 to 2 of 2

Thread: Checkbox to toggle "Private Sub workbook_SheetSelectionChange" function

  1. #1
    Neophyte Viktor86HUN's Avatar
    Join Date
    Dec 2014
    Location
    Hungary
    Posts
    4
    Articles
    0

    Red face Checkbox to toggle "Private Sub workbook_SheetSelectionChange" function



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello.

    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)


    Code:
    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
    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

  2. #2
    Add a forms checkbox on the main sheet, link it to a cell, and add this code to that sheet's code module (not ThisWorkbook)

    Code:
    Private Sub WorkSheet_SelectionChange(ByVal Target As Range)    
        If Me.Range(Me.CheckBoxes("Check Box 1").LinkedCell).Value Then
        
            With Application
        
                .ScreenUpdating = False
                .FindFormat.Clear
                .ReplaceFormat.Clear
                .FindFormat.Interior.Color = RGB(255, 230, 153)
                .ReplaceFormat.Interior.Color = xlNone
            End With
            
            Me.Cells.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
            
            With Application
              
                .FindFormat.Clear
                .ReplaceFormat.Clear
                .FindFormat.Interior.Color = xlNone
                .ReplaceFormat.Interior.Color = RGB(255, 230, 153)
            End With
            
            Target.EntireRow.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
            
            With Application
              
                .FindFormat.Clear
                .ReplaceFormat.Clear
                .ScreenUpdating = True
            End With
        End If
    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •