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

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