excelvbacheckbox

Incorporating Checkbox to lines of code rather than Cell value


I've currently got a checkbox in A17 that i want to replace the function of M9 value 0/1. How do i effectively implement this?

  If Target.Address = "$M$9" Then
  If Target.Value > 0 Then
               If ActiveSheet.ProtectContents Then
               ActiveSheet.Unprotect Password:="TL1234Kvalitet"
               Rows("20:22").EntireRow.Hidden = False
               ActiveSheet.Protect Password:="TL1234Kvalitet"
            Else
                Rows("20:22").EntireRow.Hidden = False
            End If
       Else
            If ActiveSheet.ProtectContents Then
               ActiveSheet.Unprotect Password:="TL1234Kvalitet"
               Rows("20:22").EntireRow.Hidden = True
               ActiveSheet.Protect Password:="TL1234Kvalitet"
            Else
                Rows("20:22").EntireRow.Hidden = True
           End If
       End If
   End If

Sheet

I've tried lines as:

If Target.Address = "Check Box 8" Then If Target.Value = True Then etc

Didn't quite work.. Where can i tell exactly What the check box needs to be referred to? And what would you do to make a checked box show rows and unchecked hide them..?


Solution

  • Since you did not answer my clarification question, I prepared codes for both cases, respectively:

    1. The case of ActiveX combo box, the simplest one:

    You need to double click it and paste the next code overwriting its Click event:

    Private Sub CheckBox1_Click() 'of course, you must adapt the real check box name (maybe, CheckBox8_Click)
      Dim HdRows As Range
      Set HdRows = Me.rows("20:22")
      If Me.CheckBox1.Value = True Then
         If ActiveSheet.ProtectContents Then
            Me.Unprotect Password:="TL1234Kvalitet"
             HdRows.EntireRow.Hidden = False
            Me.Protect Password:="TL1234Kvalitet"
         Else
            HdRows.EntireRow.Hidden = False
         End If
      Else
        If ActiveSheet.ProtectContents Then
            Me.Unprotect Password:="TL1234Kvalitet"
             HdRows.EntireRow.Hidden = True
            Me.Protect Password:="TL1234Kvalitet"
         Else
            HdRows.EntireRow.Hidden = True
         End If
      End If
    End Sub
    
    1. If the involved check box is Form type (most probably, looking to its name containing spaces), you need to use the next solution:

    a. Copy the next code in a Standard module:

    Sub hideRows()
     Const myChkb As String = "Check Box 8" 'use here the check box REAL name
     If Application.Caller = myChkb Then
        Dim ws As Worksheet, chkName As String, chkB As CheckBox, HdRows As Range
        chkName = myChkb
        Set ws = ActiveSheet: Set HdRows = ws.rows("20:22")
        Set chkB = ActiveSheet.CheckBoxes(chkName)
        If chkB.Value = 1 Then
            If ws.ProtectContents Then
               ws.Unprotect Password:="TL1234Kvalitet"
                HdRows.EntireRow.Hidden = False
               ws.Protect Password:="TL1234Kvalitet"
            Else
               HdRows.EntireRow.Hidden = False
            End If
         Else
           If ActiveSheet.ProtectContents Then
               ws.Unprotect Password:="TL1234Kvalitet"
                HdRows.EntireRow.Hidden = True
               ws.Protect Password:="TL1234Kvalitet"
            Else
               HdRows.EntireRow.Hidden = True
            End If
        End If
     End If
    End Sub
    

    b. Right click on the combo box and from the context menu choose Assign Macro...

    Select hideRows (the one copied above) and press OK.

    Now, you can play with checking - unchecking the respective check box and see the rows to be hidden - visible.

    Please, send some feedback after testing it.