CODEWITHSUNDEEP
vba
Punchlinern
Punchlinern

Reputation: 754

Excel - How to add checkbox with VBA and linking to cell next to it?

I'm writing a schedule in Excel with a lot of conditional formatting which alerts me if a person gets overlapping tasks. If someone doesn't show up someone else has to take several spots at the same day, then I want to add that to the spreadsheet to remember that.

The problem is that if I do so I get red cells alerting me, and I don't want that for passed dates. I thought that I might add a column called "ignoreErrors", or something like that, with checkboxes and if I check any of these Excel ignores the rest of the formatting rules.

I found the following script here (I edited a little bit):

Sub AddCheckBoxes()
    On Error Resume Next
    Dim c As Range, myRange As Range
    Set myRange = Selection
    For Each c In myRange.Cells
        ActiveSheet.checkboxes.Add(c.Left, c.Top, c.Width, c.Height).Select
        With Selection
            .LinkedCell = c.Address
            .Characters.Text = ""
            .Name = c.Address
        End With
        c.Select
    Next
    myRange.Select
End Sub

When I add the checkboxes I get a range of checkboxes, but with the cell value (TRUE or FALSE) in the background. I don't want that. I thought I would link the checkboxes to a cell in the column next to it, and hide that. Is that possible?

Upvotes: 2

Views: 28825

Answers (2)

Mark A Dvorak
Mark A Dvorak

Reputation: 11

I just make the font color in the linked cell white so you cant see the true/false.

Simple but effective :)

Upvotes: 1

Bharath Raja
Bharath Raja

Reputation: 181

If you delete the line (or comment it)

.LinkedCell = c.Address

it will not show the True or false.

To link it to the next column, do the following

.LinkedCell = c.Offset(0, 1).Address

Hope this helps.

Upvotes: 5

Related Questions