Excel VBA - Need help with a _calculate event

C

converse

Here is my situation:

I have about 10 VBA check boxes. When the user clicks any box,
formula in cell A1 does some calculations that result in a valu
between 1 and 10.

What I would like to do is if the number is 1, attention will be draw
to check box number 1 (graphic will pop up or check box will chang
color, etc.)

If the number is 2, attention will be drawn to check box number 2, the
check box number 1 will return to normal (will not be marked by graphi
or color change any longer), etc.

I'm thinking that a workbook_calculate would do it in conjuction wit
having VBA graphics boxes that are either '.visible = TRUE' o
'.visible = FALSE'. (I would prefer if the checkbox itself woul
somehow change its formatting (color or font)), but I'm not sure wher
to even really start.

Unfortunately, I know very little VBA and cannot seem to be able to ge
the this working.

Any help would be greatly appreciated.

Thanks,
Curtis
 
P

Patrick Molloy

not sure what you mean by VBA checkboxes. on a worksheet
you can use the 'forms' checkboxes or the 'controls
toolbox' checkboxes. You can use these on userforms as
well.
I'll assume for now that you're using the controls
toolbox checkboxes - these are the ActiveX controls.

I dropped four onto a spreadsheet.
Range A1 has this formula:= =INT(RAND()*4+1)
this just generates a randon integer between 1 and 4

on the sheet's code page I have this code:=

Option Explicit
Private Sub CheckBox1_Click()
recalc
End Sub
Private Sub CheckBox2_Click()
recalc
End Sub
Private Sub CheckBox3_Click()
recalc
End Sub
Private Sub CheckBox4_Click()
recalc
End Sub

Sub ResetAll()
CheckBox1.BackColor = &HE0E0E0 'grey
CheckBox2.BackColor = &HE0E0E0 'grey
CheckBox3.BackColor = &HE0E0E0 'grey
CheckBox4.BackColor = &HE0E0E0 'grey
End Sub
Sub SetBox(index As Long)
ResetAll
Select Case index
Case 1: CheckBox1.BackColor = &HFF& 'red
Case 2: CheckBox2.BackColor = &HFF& 'red
Case 3: CheckBox3.BackColor = &HFF& 'red
Case 4: CheckBox4.BackColor = &HFF& 'red
End Select
End Sub
Sub recalc()
Range("a1").Calculate
SetBox Range("A1").Value
End Sub

each time you click a checkbox, the range is calculated
and the result is used to select one of the checkboxes

not great, but it should help to give you some ideas.
keep in mind that clicking a checkbox also changes its
value, so testing a checkbox value change event can lead
to a stack overflow as the code can easily get recursive.

Patrick Molloy
Microsoft Excel MVP
 
Top