VBA to create and delete checkbox based on cell value

DYB

New Member
Joined
Jan 12, 2021
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Attachments

  • Form.png
    Form.png
    42.3 KB · Views: 12

DYB

New Member
Joined
Jan 12, 2021
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Zot

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

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,016
Office Version
  1. 2016
Platform
  1. Windows
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 (y)
 
  • Like
Reactions: DYB

Watch MrExcel Video

Forum statistics

Threads
1,127,650
Messages
5,626,084
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top