Checkbox macro

L

lorib01

Joined
Jan 5, 2007
Messages
2
Reaction score
0
Hello,

I'm a newbie to this but I've been tasked with creating a form with 55 checkboxes. I need for each one to change color whenever the box is checked or go back to white when unchecked. Every macro I try to right produces a new error. Is their a simple way to code this?

I created the checkboxes from the forms toolbar using copy/paste.

Thanks in advance.
 
Hello,

I have the following code but it requires me to make some change on the work sheet such as putting the curser in an empty cell and hitting the delete key.

This is sort of convoluted. Can anyone help me adjust the code so that box just changes color?

Thanks
Code: 
Private Sub Worksheet_Change(ByVal Target As Range) 
Dim sh As Shape 
Dim rng As Range 
	For Each sh In ActiveSheet.Shapes 
		'Shades All Checked "Forms" Checkboxes 
		If sh.Type = msoFormControl Then 
			With sh.OLEFormat.Object 
			If sh.FormControlType = xlCheckBox Then 
			.ShapeRange.Fill.Visible = IIf(Sgn(.Value) > 0, -1, 0) 
			.ShapeRange.Fill.ForeColor.SchemeColor = 22 '15 
			End If 
			End With 
		End If 
		'Shades All Checked "Control Toolbar" Checkboxes 
		If sh.Type = msoOLEControlObject Then 
			If TypeName(sh.OLEFormat.Object.Object) = "CheckBox" Then 
				If sh.OLEFormat.Object.Object = True Then 
				sh.OLEFormat.Object.Object.BackColor = &HE0E0E0 
				Else 
				sh.OLEFormat.Object.Object.BackColor = &HFFFFFF 
				End If 
			End If 
		End If 
	Next sh 
End Sub
 
Back
Top