Check Box - Hiding/Unhiding Rows

[Weaklink]

First time using macros and check boxes. My macro is not working, surprise. Excel 2010.

I have created a check box (forms control) labeled "Financial (Contract Renewal)". Is this the box/object's designation? This is what I "point to" in my macro? Am a limited in syntax what I name my check box? i.e. no spaces, special characters, etc?

I created a macro called "Macro1". I could not use "Financial (Contract Renewal)". Is that becuase no special characters or spaces allowed, or because that "label" is already in use by the object?

Anyways, I'm sure I have multiple error in here.... This will not run.

Sub Macro1()
'
' Macro1 Macro
' Hide/Unhide Rows
'
' Keyboard Shortcut: Ctrl+j
'
If Financial (Contract Renewal).Value = True then
Rows("20:31").Select
Selection.EntireRow.Hidden = False
ElseIf Financial (Contract Renewal).Value = False then
Rows("20:31").Select
Selection.EntireRow.Hidden = True
End If
End Sub

Any help is much appreciated....

 

[Gary McMaster]

Try like this:

Public Sub Test()

Dim oCheckBox As Shape

Set oCheckBox = ActiveSheet.Shapes("Financial (Contract Renewal)")

If oCheckBox.ControlFormat.Value = Checked Then
    ActiveSheet.Range("20:31").EntireRow.Hidden = True
Else
    ActiveSheet.Range("20:31").EntireRow.Hidden = False
End If

End Sub

 

[Weaklink]

Nope, did not work.

 

[Gary McMaster]

Well, I don't have 2010 but I'm sure it will work if the name of your checkbox is indeed "Financial (Contract Renewal)". That is the name that should appear in the "Name" box (left end of the formula bar 2007) if you select the check box while in design mode. If there is a different name in the "name box" then change the code to match.

Spaces and special characters are not allowed as procedure names. Even if they were, it is generally not a good idea to duplicate names to refer to different things.

Also, in your original code, you almost NEVER have to use "Select" to refer to a range. You usually only have to use "Select" if you expect the user to interactively designate a range while the code is running. In this particular case your code would have failed where you are trying to "Select" hidden cells in order to unhide them.

Gary

 

[Gary McMaster]

We are apparently not on the same page with XL versions, type of control, control name and who knows what else. I hope the following sample will eliminate all of that confusion by automatically creating a demonstration from scratch.

Please place the following code in a standard module in a new workbook and then run the "CB_Create" procedure. This works for XL2007 but I have no way to try it on XL2010.

Gary

Option Explicit

Public Sub CB_Create()

Dim oCheckBox As CheckBox
Dim oCell As Range
Dim oShape As Shape

Dim lWidth As Long
Dim lHeight As Long

lWidth = 300
lHeight = 12

'Get rid of existing controls to avoid multiple copies & duplicate names
For Each oShape In ActiveSheet.Shapes
   Debug.Print oShape.Name
   If oShape.Type = msoFormControl Then
    oShape.Delete
   End If
Next oShape

Set oCell = ActiveSheet.Range("D6")

'Create a checkbox and assign the "CB_Toggle" procedure to run when clicked
Set oCheckBox = ActiveSheet.CheckBoxes.Add(oCell.Left, oCell.Top, lWidth, lHeight)
With oCheckBox
    .Name = "CheckBox_" & oCell.Address
    .Caption = "Click me to run CB_Toggle procedure"
    .OnAction = "CB_Toggle" 'Run this procedure automatically when clicked ("Assign Macro")
    '.LinkedCell = oCell.Address
    '.Interior.ColorIndex = 3
    'Whatever other properties
End With

Set oCell = ActiveSheet.Range("D10")

'Create a second checkbox but do not assign a macro
Set oCheckBox = ActiveSheet.CheckBoxes.Add(oCell.Left, oCell.Top, lWidth, lHeight)
With oCheckBox
    .Name = "CheckBox_" & oCell.Address 'This unique name needed to ID this check box
    .Caption = "Toggle me and then run the CB_Manual procedure from the macro menu"
    'Whatever other properties
End With

End Sub

Public Sub CB_Toggle()

Dim oCheckBox  As Shape

Set oCheckBox = ActiveSheet.Shapes(Application.Caller) 'Name passed by OS

If oCheckBox.ControlFormat.Value = Checked Then
    ActiveSheet.Range("19:21").EntireRow.Hidden = True
Else
    ActiveSheet.Range("19:21").EntireRow.Hidden = False
End If

End Sub

Public Sub CB_Manual()

Dim oCheckBox  As Shape

Set oCheckBox = ActiveSheet.Shapes("CheckBox_$D$10") 'Name known, assigned when created

If oCheckBox.ControlFormat.Value = Checked Then
    ActiveSheet.Range("19:21").EntireRow.Interior.ColorIndex = 3
Else
    ActiveSheet.Range("19:21").EntireRow.Interior.ColorIndex = 4
End If

End Sub

 

[mikerickson]

To test the state of a checkbox from the Forms menu

Sub Macro1()
    Range("20:30").EntireRow.Hidden = Not (ActiveSheet.Shapes("Financial (Contract Renewal)").ControlFormat.Value = xlOn)
End Sub

 

[hmirgol]

I want to hide( not delete) certain rows in my worksheet.
wanna to hise rows 5,6 and 8,9 and 11,12,....and periodoically. already I have a sheet including 75000 rows, so need to macro is undeniable...
your help is appreciated in advance