Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Need help with a _calculate event
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Need help with a _calculate event
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 -----Original Message----- Here is my situation: I have about 10 VBA check boxes. When the user clicks any box, a formula in cell A1 does some calculations that result in a value between 1 and 10. What I would like to do is if the number is 1, attention will be drawn to check box number 1 (graphic will pop up or check box will change color, etc.) If the number is 2, attention will be drawn to check box number 2, then check box number 1 will return to normal (will not be marked by graphic or color change any longer), etc. I'm thinking that a workbook_calculate would do it in conjuction with having VBA graphics boxes that are either '.visible = TRUE' or '.visible = FALSE'. (I would prefer if the checkbox itself would somehow change its formatting (color or font)), but I'm not sure where to even really start. Unfortunately, I know very little VBA and cannot seem to be able to get the this working. Any help would be greatly appreciated. Thanks, Curtis. --- Message posted from http://www.ExcelForum.com/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user form-on open event? keydown event? | Excel Programming | |||
Event Procedures: Event on Worksheet to fire Event on another Worksheet | Excel Programming | |||
change event/after update event?? | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming |