Add check boxes to active sheet using VBA

[LostOracle]

Hi All

I'm not sure this is possible but I came across some VBA code that allowed you to add check boxes to an active sheet based on the selection made, unfortunately the check box name remains the default "Check Box #" convention, I have successfully modified the code to add the correct prefix.

I have a need to name each check box on the first row selection as cbx_1_1, cbx_1_2, cbx_1_3, the second row would then be cbx_2_1, cbx_2_2, cbx_2_3 and so on.

Sub insertCheckboxes()


Dim Rng As Range
Dim WorkRng As Range
Dim Ws As Worksheet
Dim iRowFirst As Long
Dim iColCount As Integer
Dim RowCount As Integer


iColCount = 1


On Error Resume Next
xTitleId = "Insert Check Boxes"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Ws = Application.ActiveSheet
iRowFirst = WorkRng.Row
Application.ScreenUpdating = False
For Each Rng In WorkRng
    With Ws.CheckBoxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height)
        .Characters.Text = Rng.Value
        .Name = "cbx_" & iRowFirst & "_" & iColCount
    End With
iColCount = iColCount + 1
iRowFirst = iRowFirst + 1
Next
WorkRng.ClearContents
WorkRng.Select
Application.ScreenUpdating = True
End Sub

I can get each check box named with the prefix cbx_ and get each entry to iterate across columns but I am struggling to have each new row iterate by 1 to get the desired format stated above.

Would appreciate some guidance and your comments are welcomed.

 

[NoSparks]

I have a need to name each check box on the first row selection as cbx_1_1, cbx_1_2, cbx_1_3, the second row would then be cbx_2_1, cbx_2_2, cbx_2_3 and so on.

Can you elaborate as to what 'and so on' is? If using the Input Box the user selected this

$A$1:$D$1,$C$4:$G$4,$A$7:$C$7
​

The name of each of the 12 check boxes would be what ?

 

[LostOracle]

Hi NoSparks

Thank you for your reply and I hope that an image would explain what I want to achieve better than perhaps my verbal explanation.

http://img4.imagetitan.com/img.php?image=18_excel.fw.png

On an active spreadsheet pressing ALT+F8 will bring up the cell selection box, by selecting multiple cells the range is determined and the code will then insert the required checkboxes into the cells selected. What I would like to achieve is the checkbox names on the first row to be cbx_1_1, cbx_1_2 & cbx_1_3. The second row in the selection would then be named cbx_2_1, cbx_2_2 and cbx_2_3. This would continue down and along the remaining checkbox entries with the same pattern.

The starting row and column could be any where on the active sheet and the selection made could just be a few cells to many cells, I hope this helps explain better.

 

[NoSparks]

Based on your examples, this type of thing should work
IF the selection is all at once or by rows in order.
Will not work if selection is by column, rows not in order or helter skelter.

Sub ChkBoxesWithNames()

Dim Ws As Worksheet, WorkRng As Range, Rng As Range
Dim iRowCount As Long, iColCount As Long, iRowNum As Long
Dim iRowFirst As Long, xTitleId As String

xTitleId = "Pick Where To Insert Check Boxes"

On Error Resume Next
Set WorkRng = Application.InputBox("Range", xTitleId, Type:=8)
On Error GoTo 0
If WorkRng Is Nothing Then Exit Sub

Set Ws = Application.ActiveSheet

iRowNum = Split(Mid(Replace(WorkRng.Address, ":", ""), 2), "$")(1)
iColCount = 1
iRowCount = 1

For Each Rng In WorkRng
    With Ws.Checkboxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height)
        .Characters.Text = Rng.Value
        If Rng.Row = iRowNum Then
            Rng.Value = "cbx_" & iRowCount & "_" & iColCount
            iColCount = iColCount + 1
        Else
            iRowNum = Rng.Row
            iRowCount = iRowCount + 1
            iColCount = 1
            Rng.Value = "cbx_" & iRowCount & "_" & iColCount
            iColCount = iColCount + 1
        End If
        .Name = "cbx_" & iRowFirst & "_" & iColCount
    End With
Next Rng
End Sub

 

[LostOracle]

NoSparks

That's absolutely brilliant and and like to extend my gratitude to you as this small VBA code will save a massive amount of time. I made some slight changes to get it exactly the way I wanted but that wouldn't have been possible without your assistance, so thank you.

The slightly modified VBA code for reference:

Sub ChkBoxesWithNames()

Dim Ws As Worksheet, WorkRng As Range, Rng As Range
Dim iRowCount As Long, iColCount As Long, iRowNum As Long
Dim iRowFirst As Long, xTitleId As String


xTitleId = "Pick Where To Insert Check Boxes"


On Error Resume Next
Set WorkRng = Application.InputBox("Range", xTitleId, Type:=8)
On Error GoTo 0
If WorkRng Is Nothing Then Exit Sub


Set Ws = Application.ActiveSheet


iRowNum = Split(Mid(Replace(WorkRng.Address, ":", ""), 2), "$")(1)
iColCount = 1
iRowCount = 1


For Each Rng In WorkRng
    With Ws.CheckBoxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height)
        .Characters.Text = Rng.Value
        If Rng.Row = iRowNum Then
            Rng.Value = "cbx_" & iRowCount & "_" & iColCount
            iColCount = iColCount + 1
        Else
            iRowNum = Rng.Row
            iRowCount = iRowCount + 1
            iColCount = 1
            Rng.Value = "cbx_" & iRowCount & "_" & iColCount
            iColCount = iColCount + 1
        End If
        .Name = "cbx_" & iRowCount & "_" & iColCount - 1
    End With
Next Rng
End Sub