Hide and unhide a activeX checkboxes based a a Targetvalue

[Tanner_2004]

I am using the code below unsucessfully:


ActiveSheet.Shapes.Range("Function").Visible = msoFalse
ActiveSheet.Shapes.Range("Function").Visible = msoTrue

The worksheet contains 10 activeX check boxes that I have assigned all the same GroupName "Function"

Basically, If Target.Value = "Hub" Then I want to show (make visible) all of the checkboxes
If not "Hub", then I do not want to show any of the checkboxes (make unvisible).

The checkboxes are named cbF1 through cbF10.

Any ideas woudl be greatly appreaciated.

Thank you!

 

[tlowry]

Try this in the sheet's module...

To test; change A1 to "Hub" and then not "Hub"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells(1, 1).Address = "$A$1" Then
        Dim cb
        For Each cb In ActiveSheet.Shapes
            If cb.DrawingObject.Object.GroupName = "Function" Then _
                                        cb.Visible = Target.Cells(1, 1).Value = "Hub"
        Next
    End If
End Sub

 

[Tanner_2004]

Thank you. But I didn't really know how to incorporate it into the existing code. This is the code that used to work. I actually had all of the checkboxes in a group.
The code worked like a charm, but then all but the top and bottom (the first and last of 10) checkboxes would be visible. It was like checkboxes 2 through 9 would disappear after running the code several times.

This is what used to work:
Private Sub Worksheet_Change(ByVal Target As Range)

'Target is a special variable set up
' by Microsoft to represent the cell that was
' just changed

' See if they changed the same row as the SOL cell
If Target.Row = Range("Role").Row Then
If Target.Column = Range("Role").Column Then
' we know they just changed the Role cell


'This message box only pops-up if Hub is selected
If Target.Value = "Hub" Then

If Range("Role") = "Hub" Then

' This sets the variable Z to the MsgBox value
' Ok = 1, Cancel = 2

Z = MsgBox("You selected hub as a role for this country." _
& " It is likely that this hub will provide support to the " _
& " program among one or more areas. If you your intention" _
& " was to select ""hub"" click OK, otherwise click Cancel, and" _
& " choose another role for this country. Thank you.", _
vbOK, "Hub Selection")

Range("FunctionResponseRange").Value = False


End If


Rows("8:19").EntireRow.Hidden = False
'This line of code displays checkboxes 1 through 10 as part of
' cbFunctionGroup
ActiveSheet.Shapes.Range(Array("cbFunctionGroup")).Visible = msoTrue


Else

Rows("8:19").EntireRow.Hidden = True
'This line of code hides checkboxes 1 through 10 as part of
' cbFunctionGroup
ActiveSheet.Shapes.Range(Array("cbFunctionGroup")).Visible = msoFalse



End If
End If
End If

End Sub