Adding checkboxes from VBA form into Excel spreadsheet

Question

I am trying to fill a range that is unknown in size, as a user can choose up to 15 items in the first set. This will be inserted into a certain row.

I have a checkbox with the following names/values:

Name         Value
==========   =====
chk_week1    1
chk_week2    2
...          ...
...          ...
chk_week15   15

For example if the user selects chk_week1, chk_week2, chk_week4 and chk_week5, then it should be inserted into the cell as 1,2,4,5.

I've included an image how it looks like to better demonstrate it:

enter image description here

Each checkbox has the name and value listed in the table above. Here is the code I am using so far:

Private Sub btnSubmit_Click()

Dim ws As Worksheet
Dim rng1 As Range
Set ws = Worksheets("main")

' Copy the data to the database
' Get last empty cell in column A
Set rng1 = ws.Cells(Rows.Count, "a").End(xlUp)

deptCodeSplit = Split(cbo_deptCode.Value, " ")

' Having difficulty adding the code here
' rng1.Offset(1, 6) = weeks

End Sub

Jüri Ruut · Accepted Answer · 2012-10-30 15:16:16Z

1

Value of a marked checkbox is shown in Linkedcell. Both can be assigned from Checkbox Properties.

Let's assign values from 1 to 15 for every separate checkbox, Linkedcells are in cells A1 to A15, having unique values from 1 to 15 for ticked checkboxes, or are blank for checkboxes which are not selected.

Corresponding cells in column B will be used for sequential merging:

B1:

=IF(A1<>"",A1,"")

B2 to Bn:

=IF(AND(A2<>"",B1<>""),B1&" ,"&A2,IF(AND(B1="",A2<>""),A2,B1))

The formula can be copied downwards indefinitely, the required string will be in the last row.

To accomplish the same in VBA:

Function ValuesFromRange(Rng As range, Optional Delimiter As String)

Dim c As range
Dim txt As String

If Delimiter = "" Then Delimiter = ","
  txt = ""

   For Each c In Rng
     If Len(c.Value) > 0 Then
       If Len(txt) = 0 Then
          txt = c.Value
        Else
          txt = Trim(txt) & Delimiter & c.Value
        End If
     End If
   Next

 ValuesFromRange = txt

End Function

Spreadsheet example: http://www.bumpclub.ee/~jyri_r/Excel/ValuesFromRange.xls

edited Oct 30, 2012 at 15:16

answered Oct 29, 2012 at 12:21

Jüri Ruut

2,53019 silver badges20 bronze badges

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

2 Comments

methuselah Over a year ago

How can I achieve this in VBA?

Jüri Ruut Over a year ago

I discovered that I actually had VBA solution written and well-forgotten ;-)

Collectives™ on Stack Overflow

Adding checkboxes from VBA form into Excel spreadsheet

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