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
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..?
Since you did not answer my clarification question, I prepared codes for both cases, respectively:
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
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.