Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Control Checkbox Properties (Excel 2003 )

    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, ([img]/forums/images/smilies/cool.gif[/img] 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.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Checkbox Properties (Excel 2003 )

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

    <pre>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
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Checkbox Properties (Excel 2003 )

    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •