Control Checkbox Properties (Excel 2003 )

Topic

New Reply

Good morning to all…

Today’s challenge (at least for me it’s a challenge) is to figure out how to create a series of checkboxes using VBA so that a user can select certain records in a list. My task is to create a target list of items from a larger list (already done) so that the user will be able to select certain records from that target list. What I’d like to do is to not only create the target list, but to also create a checkbox next to each record in the target list so that the user simply needs to click the checkbox so that record will undergo further processing. Right now I am using data validation to get this done, but it would be more elegant to have the checkbox.

I know there is a cell link in the Control checkboxes properties, but I don’t know how to (a) programatically create the checkbox, ( place it next to the record, and © establish the linked cell.

I will be grateful for any advice…

As always, I am deeply appreciative of the great help I get here.

Reply | Quote

Replies

This code adds checkboxes to the right of the current selection:

Option Explicit

Sub AddBoxesToRange()
    Dim oCell As Range
    Dim oBox As CheckBox
    If TypeName(Selection)  "Range" Then Exit Sub
    For Each oCell In Selection.Columns(Selection.Columns.Count).Cells
        Set oBox = oCell.Parent.CheckBoxes.Add(oCell.Offset(, 1).Left, oCell.Offset(, 1).Top, _
        oCell.Offset(, 1).Width, oCell.Offset(, 1).Height)
        oBox.LinkedCell = oCell.Offset(, 2).Address(external:=True)
    Next
End Sub

Reply | Quote

Hi Jan Karel,

Thank you for the code. It works a charm.

Now I need to incorporate it into my larger model, but I won’t be able to until next week. It looks like exactly what I need.

Thank you again…

Reply | Quote