Check-box should not be marked/unmarked when the sheet is protected

Question

I am trying to make a macro that adds a new checkbox when new data is inputed, but it should only be marked or unmarked when the sheet is unprotected. I tried to do this with the following code:

Range("Z4").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(2,1/(C[-15]<>""""),ROW(C[-15]))"
i = Range("Z4").Value + 1

    Cells(i, 19).Select
    ActiveSheet.CheckBoxes.Add(Cells(i, 19).Left, Cells(i, 19).Top, 60.75, 15).Select
    With Selection
            .Value = xlOff
            .LinkedCell = Cells(i, 30)
            .Display3DShading = False
    End With
    Selection.Characters.Text = "Pagado"

Where the linked cell is locked and therefore should not be able to be changed when locked.

Otherwise, the rest of the code works perfectly.

So what is the problem exactly? Side note: you generally want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Feb 04 '20 at 20:06
@cybernetic.nomad The issue is that I require that the checkbox is not marked or unmarked while the sheet is protected. But, in order to avoid the manual configuration of setting up the linked cell, I am looking for a way where when the code adds a new checkbox, it links it with the cell located at the same row but column 30, but the code is not doing so right now — Ruskyrou, Feb 04 '20 at 20:51

score 0 · Accepted Answer · answered Feb 04 '20 at 22:00

So I figured it out by doing this, was easier that I thought. It was a matter of adding two strings together and then protecting the sheet and locking the referenced cells.

Cells(i, 19).Select
ActiveSheet.CheckBoxes.Add(Cells(i, 19).Left, Cells(i, 19).Top, 60.75, 15).Select
With Selection
        .Value = xlOff
        .LinkedCell = "$CC$" & i
        .Display3DShading = False
End With
Selection.Characters.Text = "Pagado"

Hope it can help someone else.

Check-box should not be marked/unmarked when the sheet is protected

1 Answers1