Solved Checkbox Macro for Excel Depending on Tab Color

Microsoft Office excel 2007 home & stude...
January 13, 2012 at 14:12:39
Specs: Windows XP
I have a workbook with mutliple worksheets. When a worksheet gets updated, we change the tab color so we know which sheets have been updated. On the First Sheet (Home Sheet) we have coresponding check boxes that relate to each sheet (tab). We would check the boxes if a certain (sheet) tab has been updated. I'm trying to write a Macro to where it would reference the tab color (thanks DerbyDad03 for your help), and automatically check the corresponding check box. I hope this makes sense.

Part of the code looks like this:
If Sheets(Sheet4).Tab.ColorIndex = 46 Then
Sheets(Sheet1).CheckBox4.Value = Checked
End If
The Macro runs, but the check box doesn't get checked. I've tried to play around with CheckBox4.Value = True, CheckBox4.Value = Enabled, CheckBox4.Click = Checked, etc...

Any suggestions? Any help is much appreciated. Thanks in advance!

See More: Checkbox Macro for Excel Depending on Tab Color

Report •

January 13, 2012 at 15:21:16
I've never done anything with CheckBoxes in Excel, but I did do some Google searches.

You could try setting the Value to -1 or 1 or 0

I also found this workaround:

Junior Contributor

Heres the work around I ended up with for the same problem:

1) Pick a row that will not be used for your work book
2) Right Click on the Checkbox
3) Select Format Control
4) On the control tab you'll see a cell link field
5) Assign the checkbox to a cell in the row you chose
6) Do this for each checkbox giving them each a different cell in the row
7) Now you can put the value True or False into the cells
8) The checkboxes will respond accordingly

Just to note I selected the row and hid it so the user would not see the value. I just change the values in the hidden row using VB and the correct checkboxes become highlighted. It works great for me.

Reply With Quote

Junior Contributor

Join Date: Sep 2001
Location: Igloo # 7, Canada
Posts: 332
That worked great! I didn't even think of using Form controls. I was using controls from the Control Toolbox because I was trying to refer to their names through code. Using the Form controls and then just linking them with cells on the form is a great idea!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 13, 2012 at 17:00:13
The Value to -1 or 1 or 0 didn't work.

I did try the workaround...and it seems like that would work....but now is another question....

Is there a way I can update the Macro to insert the "value" in a cell on the coresponding sheet?

For example:
Sheet 2 (if tab color is orange), insert "1" into cell A1 and then I can use the cell link field for the check box.
Sheet 3 (if tab color is orange), insert "1"...etc....?

Report •

January 13, 2012 at 20:16:53
✔ Best Answer
If Sheets(Sheet4).Tab.ColorIndex = 46 Then
   Sheets(Sheet1).Range("A1") = 1
End If

Please click on the following line and read the instructions found there before posting any more code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

January 17, 2012 at 14:16:38
This should work! Thanks again DerbyDad03!

Report •

Ask Question