VBA CheckBox issues

[cjt]

I have a macro which generates a new spreadsheet and within that spreadsheet it creates several check boxes and a button. The button is assigned a macro which should hide or show the check boxes if they are pertinent or not. When I attempt to run the code through the button I get the following error: Run Time error 424 Object Required. Here's some of the code:


The Check boxes are created like this:

ActiveSheet.CheckBoxes.Add(Left:=Range("E6").Left, Top:=Range("E6").Top, Width:=Range("E6").Width, Height:=Range("E6").Height).Select
        With Selection
            .Name = CheckBox1
            .Caption = "All personal transactions have been properly Flagged"
        End With

Then a separate sub contains the code for hiding or showing them attached to the button like this:

  If Sheets("Review Report").Range("B6").Value > 0 Then
        Sheets("Review Report").Range("B6").Interior.ColorIndex = 27
        CheckBox1.Visible = True
    Else:
        Sheets("Review Report").Range("B6").Interior.ColorIndex = 0
        CheckBox1.Visible = False

The error comes from this line:

CheckBox1.Visible = True

I wonder if the problem comes from the fact that the checkboxes are generated in one macro and then the second macro cannot find those?

[Charlize]

I think the .visible property is an only read one, ie. you can check the status but that's the only thing you can do.
To alter the visibility of an object, try using .Hidden = False to see if you can see the checkbox.
Think worksheets work the same way.

Charlize

ps. or maybe add the sheetreference to the checkbox ?

[cjt]

I finally figured it out this worked:
ActiveSheet.CheckBoxes(1).Visible = True

I'm not sure why that works but just putting: SheetName.CheckBox1.Visible = True
doesn't work. Thanks for the help though!

[Charlize]

Forget what I said . You have to loop through the boxes you made on your sheet.

Sub test()
Dim theboxes As Long
ActiveSheet.CheckBoxes.Add(Left:=Range("E6").Left, Top:=Range("E6").Top, Width:=Range("E6").Width, Height:=Range("E6").Height).Select
With Selection
    .Name = CheckBox1
    .Caption = "All personal transactions have been properly Flagged"
End With
If ActiveSheet.CheckBoxes.Count > 0 Then
    For theboxes = 1 To ActiveSheet.CheckBoxes.Count
        MsgBox "Name : " & ActiveSheet.CheckBoxes(theboxes).Name
    Next theboxes
End If
End Sub

Charlize

[snb]

@cjt can you please use code tags around the VBA-code ? Like

Sub M_snb()
   with ActiveSheet.CheckBoxes.Add(Range("E6").Left, Range("E6").Top, Range("E6").Width, Range("E6").Height)
     .name="snb_001"
     .Caption = "All personal transactions have been properly Flagged"
   end with
End Sub

after that you can refer to those checkboxes with either

sub M_snb_001()
   ActiveSheet.CheckBoxes(1).Visible = Not ActiveSheet.CheckBoxes(1).Visible
end sub

or

Sub M_snb_002()
   ActiveSheet.CheckBoxes("snb_001").Visible = Not ActiveSheet.CheckBoxes("snb_001").Visible
End Sub

[cjt]

Ok I will definitely use the code tags from now on, I'm new to this thanks!