VBA to create and delete checkbox based on cell value

[DYB]

I am a novice at VBA but am using some code to automatically create checkboxes on a form based on the value of the cell.

The value of the cell is determined by an 'iferror/index/match' formula and the 'iferror' part of the formula returns a blank value. I want the checkboxes to appear only if the formula result is an actual 'index' value. The VBA code below does work but my problem is that the checkboxes never disappear due having a formula in the cell. If I delete the formula the checkbox disappears but I need the formula to remain in the cells in each row as the number of rows containing actual values will vary depending on the number of 'index/match' values it finds. The form populates the rows based on the application type selected from a drop down list in cell A6 (snapshot of form attached)

Can someone help amend the code below so that it ignores formula written in cells and only reacts to actual values.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim chkbox As CheckBox
Dim cell As Range

If Not Intersect(Target, Range("B11:B1000")) Is Nothing Then
For Each cell In Intersect(Target, Range("B11:B1000"))
If Not IsEmpty(cell.Value) Then
'If the cell is NOT empty, I should add a checkbox, to the right of the cell without text
Set chkbox = Sheet1.CheckBoxes.Add(cell.Left, cell.Top, cell.Width, cell.Height)
With chkbox
.Text = ""
End With
Else
For Each chkbox In Sheet1.CheckBoxes
If Not Intersect(cell, chkbox.TopLeftCell) Is Nothing Then
chkbox.Delete
End If
Next chkbox
End If
Next cell
End If
End Sub

 

[DYB]

Hi Zot

Thank you so much for all your help, that works perfectly!

 

[Zot]

Good to hear that it works.

I should have added

Application.ScreenUpdating = False before that For Each Loop. The screen will look more brisk.

I've never thought of listing only necessary item instead of listing all items with CheckBoxes on their sides. I learned something trying to write codes for this