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.