PDA

View Full Version : [SOLVED] VBA CheckBox issues



cjt
12-04-2014, 11:39 AM
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
12-04-2014, 11:55 AM
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
12-04-2014, 12:03 PM
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
12-04-2014, 12:12 PM
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
12-04-2014, 01:36 PM
@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
12-04-2014, 02:02 PM
Ok I will definitely use the code tags from now on, I'm new to this thanks!