Checkbox

  • Thread starter Thread starter cadbury
  • Start date Start date
C

cadbury

I need to highlight (color-fill) a cell when a checkbox is clicked an
then be able to unclick and have the cell have no-fill.

anyone know how to do this?

Cadbur
 
Where is your checkbox? On UserForm or Worksheet?

Anyway, to color a cell, for example, yellow:

range("A1").Interior.ColorIndex = 6

To remove the color:
range("A1").Interior.ColorIndex = xlNone

To discover more "colorindex", you may try using the "Record Macro" function of Excel.

In fact, the Record Macro function is a very very good way to learn VBA.

Regards,
Edwin Tam
(e-mail address removed)
 
So, create the following macro in a module, then assign it to the checkbox on your worksheet.

Sub CheckBox1_Click()
With ActiveSheet.CheckBoxes("Check Box 1")
If .Value = xlOn Then
Range("A1").Interior.ColorIndex = 6
Else
Range("A1").Interior.ColorIndex = xlNone
End If
End With
End Sub

----- cadbury wrote: -----


The Checkbox is in a worksheet.
 
Cadbury,

Here is an approach where you don't need VBA for this.
You can use a linked cell in combination with conditional
formatting.

1. Create the checkbox

2. If the checkbox is from the "Forms" toolbar:
- Right-Click the checkbox and select "Format Control"
- Select the Control tab
- Set cell link to A1
- Click OK

If the checkbox is fron the "Control Toolbox" toolbar:
- Right-Click the checkbox and select "Properties"
- Set the cell link property to A1
- Close the properties window
- Click the "Exit Design Mode" toolbar button

3. Select the cell you want to color

5. Format - Conditional Formatting
Formula is... =A1=TRUE
Click Format, set Red color on Patterns tab

6. Click OK, twice
 
The non-VBA is the better way for me to go, but the cell that highlights
also says "true" or "false", depending on the checkbox. How can I make
it so true or false doesn't replace the text already in the cell.

Thanks for your help

Cadbury
 
I figured out a solution. I made the text color part of the conditional
formatting, the true and false are still there, they just blend in with
the cell color. If there is a better way, please let me know.

Cadbury
 
Cadbury,

I often set the checkbox's cell link to the cell behind the
checkbox and set the number format of that cell to the
custom format ;;;
 
Back
Top