Results 1 to 2 of 2
Thread: Using the checkbox (excel 2000)
2003-07-16, 12:28 #1
- Join Date
- Mar 2003
- Thanked 0 Times in 0 Posts
Using the checkbox (excel 2000)
I am trying to figure out how to use the checkbox command on a spreadsheet. What I am trying to do is assign three checkboxes (as options which only one can be checked at a time) that concerns a particular cell. Than write a macro that will do different things based on which checkbox is selected for that cell. First, how can I get the checkboxes to work as described on a spreadsheet? Second, I am thinking of some possible way (maybe using IF statements) to write the macro, but how do I write the macro to understand the checkboxes (for instance: IF (checkbox 1) THEN.......ELSE IF (checkbox 2) THEN........ELSE IF (checkbox 3) THEN.....)? How do I write the checkbox 1,2,3 commands in the if statements so that the macro understands it. Does it have something to do with using true or false statements? And I am open to any other suggestions as to how this macro could be written.
2003-07-16, 13:50 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: Using the checkbox (excel 2000)
In the first place, if you want the choice to be exclusive (only one can be selected), it is customary to use radio buttons; check boxes are meant for situations where any combination of options can be selected, from none to all. See for instance the Tools | Options... dialog of Excel.
There are two types of controls you can use on a worksheet: those from the Forms toolbar and those from the Control Toolbox.
If you use the radio buttons from the Forms toolbar, you can right-click one of them, select Format Control... and set the Cell Link to the address of a cell. the Cell Link for the other two radio buttons will be set to the same cell. Clicking the first radio button will set this cell to 1, clicking the second radio button will set it to 2, etc. So in your macro, you can check the value of the linked cell to determine which radio button was selected. For example if A13 is the linked cell:
Select Case Range("A13")
If you use radio buttons from the Control Toolbox, you must right-click each of them in turn (while in design mode) and select Properties. Set the Linked Cell for each of them to a different cell. The linked cells will be set to TRUE or FALSE as you click the radio buttons, so you can have TRUE | FALSE | FALSE or FALSE | TRUE | FALSE or FALSE | FALSE | TRUE. In your macro, you can inspect the values of the linked cells, or use the value of the option buttons themselves (for the latter to work, you can best write the code in the worksheet module). For example, if A13:A15 are the linked cells:
If Range("A13") = True Then
ElseIf Range("A14") = True Then
ElseIf Range("A15") = True Then