Visual Basic Excel create Checkbox in Code

Question

I've added a button to an Excel file which, when clicked, reads a text file and populates a column with lines from a text file. I need to add a checkbox to cells adjacent to certain lines, depending on what the line contains.

Can I create components like checkboxes in code, and if so, how?

Any responses are appreciated.

i guess this is what you need analysistabs.com/vba/…

Siva
– Siva

2016-08-23 11:04:05 +00:00
Commented Aug 23, 2016 at 11:04 — Siva
– Siva, Commented Aug 23, 2016 at 11:04

Ralph · Accepted Answer · 2016-08-23 11:58:08Z

1

While the link @Siva provided is certainly valid I just prefer to have an answer on StackOverflow instead of an external link. Hence, here is the solution you might be looking for:

Option Explicit

Public Sub tmpSO()

Dim i As Long
Dim chk As CheckBox

With ThisWorkbook.Worksheets(1)
    .CheckBoxes.Delete
    For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
        If .Cells(i, "A").Value2 = "need checkbox" Then
            Set chk = .CheckBoxes.Add(Left:=.Cells(i, "B").Left, Top:=.Cells(i, "B").Top, Width:=.Cells(i, "B").Width, Height:=10)
            chk.OnAction = "runThisSub"
            chk.Name = "CheckBowInRow" & i
            chk.Caption = "CheckBowInRow" & i
        End If
    Next i
End With

End Sub

Sub runThisSub()

MsgBox "You clicked the checkbox " & Application.Caller _
            & Chr(10) & "in cell " & ThisWorkbook.Worksheets(1).CheckBoxes(Application.Caller).TopLeftCell.Address

End Sub

Copy both subs into a Module into your Excel file and change in the first sub

the sheet where the text is imported to (here it is Worksheet(1)),
the column where the condition can be found (here column A), and also
what the condition is (here the value in column A must be need checkbox).

The code will now look through all cells in column A in sheet Worksheet(1) and check if the value is need checkbox. If so, the code will automatically add a checkbox into column B adjacent to that cell.

If you click on any of the newly created checkboxes then the second sub fires up and will show you in a message box which checkbox in which row has been clicked.

answered Aug 23, 2016 at 11:58

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

C Banana Over a year ago

That is absolutely awesome, thanks so much! May I ask what "Option Explicit" does?

Ralph Over a year ago

Option Explicit is considered coding best practice. For more information read this: stackoverflow.com/documentation/excel-vba/1107/… and also (if you wish to read Microsoft's thought on this) msdn.microsoft.com/en-us/library/y9341s4f.aspx

Collectives™ on Stack Overflow

Visual Basic Excel create Checkbox in Code

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related