Hiding named ranges with VBA

[agvdsloot]

Hello, "Newbie" here.
I am trying to use vba to hide a named range in the main sheet with a check box. My reason for using a named range is that if I point to a list of Rows directly in VBA, if someone adds or deletes a row on the main sheet, the vba code is then pointing to the wrong rows and the wrongs rows are hidden with the check box. So to be clear, I have a sheet with a bunch of check boxes. Each check box needs to hide/unhide a named range on the main sheet.
Thanks in advance

 

[Gary McMaster]

What kind of check boxes? ActiveX or Form?

Can you give a few examples of the range names?

Gary

 

[agvdsloot]

Thanks Gary for the quick reply.
I am using an activeX check box.
The range is named "_005B"
I have code working that points to the rows I wish to hide/unhide directly but if someone adds or removes a row on the main sheet the VBA then references the wrong rows. The code looks like this.

Private Sub CheckBox2_Click()
If CheckBox2 Then
Sheet2.Rows("134:189").Hidden = False
Sheet2.Rows(19).Hidden = False
Sheet2.Cells(156, 2) = 1
Else
Sheet2.Rows("134:189").Hidden = True
Sheet2.Rows(19).Hidden = True
Sheet2.Cells(156, 2) = 0
End If
End Sub

Thanks again for your comments in advance.

 

[agvdsloot]

Also as you can see, i am populating an enable bit in cell 156,2 with either a 1 or a 0. this activates the math in the range to provide me with a value if enabled. Using the method shown, again, if someone adds a row or deletes a row, suddenly the checkbox will populate the wrong cell with a 1 or 0 hugely affecting the math totals on the sheet2.

 

[Gary McMaster]

See if this works:

Gary

Private Sub CheckBox2_Click()

If CheckBox2 Then
Sheet2.Range("_005B").Rows.Hidden = False
Sheet2.Rows(19).Hidden = False
Sheet2.Cells(156, 2) = 1
Else
Sheet2.Range("_005B").Rows.Hidden = True
Sheet2.Rows(19).Hidden = True
Sheet2.Cells(156, 2) = 0
End If

End Sub

 

[agvdsloot]

I have done some reading and have tried the following code:

Private Sub CheckBox1_Click()
If CheckBox1 = False Then 'Hide Them
sheet1.Range("_005B_enable") = 0
sheet1.Range("Adriaan").EntireRow.Hidden = True
Else
sheet1.Range("_005B_enable") = 1
sheet1.Range("Adriaan").EntireRow.Hidden = False
End If
End Sub


The named range i wish to hide/unhide is called "Adriaan" and the cell I wish to populate is called "_005B_enable".

This half works. It seems which ever action comes first in the code is completed while the other is not. The way it is now the "005B_enable " gets set with the check box but the "Adriaan" range is not hidden. If i reverse the order of the two lines in the code from this:
If CheckBox1 = False Then 'Hide Them
sheet1.Range("_005B_enable") = 0
sheet1.Range("Adriaan").EntireRow.Hidden = True
Else
sheet1.Range("_005B_enable") = 1
sheet1.Range("Adriaan").EntireRow.Hidden = False

To this:

If CheckBox1 = False Then 'Hide Them
sheet1.Range("Adriaan").EntireRow.Hidden = True
sheet1.Range("_005B_enable") = 0
Else
sheet1.Range("Adriaan").EntireRow.Hidden = False
sheet1.Range("_005B_enable") = 1

Range "Adriaan" is hidden/unhidden but range "_005B_enable" is not changed or set to a "1" or "0".

Can anyone help?

 

[Gary McMaster]

If you have a lot of check boxes it may be easier (at least easier to maintain) if you use "Form" check boxes. With the form check box you can use "Application.Caller" in a single procedure to identify which check box launched the code. You could assign all of the check boxes to the same macro (similar to that shown below).

The sample below assumes that the name of the range ("_0059" per your sample) has been assigned to the "Alternative Text" property of the text box. You could also simply name the check boxes "CB_RangeName" or per your example "CB_0059". That way you could strip off the leading "CB" to get the name of the range directly from the check box name instead of putting it in the "Alternative Text" property.

Gary

Public Sub Hide_Row()

Dim oShape As Shape

'Assign this procedure to all check boxes meant to hide rows
'Put the name of the target range in the "Alternative Text" property of each check box
Set oShape = ActiveSheet.Shapes(Application.Caller)

Debug.Print oShape.Name
Debug.Print oShape.AlternativeText

If oShape.ControlFormat.Value = Checked Then
    ActiveSheet.Range(oShape.AlternativeText).EntireRow.Hidden = True
Else
    ActiveSheet.Range(oShape.AlternativeText).EntireRow.Hidden = False
End If

End Sub

 

[agvdsloot]

Thanks Gary,
I put your code in and I get a Run-time error '1004'
Method 'Range" of object '_Worksheet" failed

 

[agvdsloot]

Thanks Gary but as stated before I am a Newbie. I feel like I am close with the ActiveX and learning the "Form" may be a little too much for me all at once.
Thanks for your help, it is greatly appreciated. can you comment on the code I put in #6.
Again thanks alot.

 

[Gary McMaster]

I have done some reading and have tried the following code:

This works for me XL2000.

Gary

Private Sub CheckBox1_Click()
    If CheckBox1 = False Then 'Hide Them
        sheet1.Range("_005B_enable") = 0
        sheet1.Range("Adriaan").EntireRow.Hidden = True
    Else
        sheet1.Range("_005B_enable") = 1
        sheet1.Range("Adriaan").EntireRow.Hidden = False
    End If
End Sub