Inserting checkbox, radio and dropdown values from VBA form into Excel spreadsheet

Question

I have a bit of a multi-faceted question regarding inserting checkbox, radio and dropdown values from a VBA form into the Excel spreadsheet. I am trying to accommodate a couple case scenarios in my code at the moment.

The first set of items I want to insert is a checkbox with the following names/values:

Name         Value
==========   =====
chk_week1    1
chk_week2    2
chk_week3    3
chk_week4    4
chk_week5    5
chk_week6    6
chk_week7    7
chk_week8    8
chk_week9    9
chk_week10   10
chk_week11   11
chk_week12   12
chk_week13   13
chk_week14   14
chk_week15   15

If the user makes a selection of several checkboxes then it should be inserted in the form 1,2,4,5 - for example if the user selects chk_week1, chk_week2, chk_week4 and chk_week5.

The second set of items I want to insert is a single pick from radio group with the followng names/values within the frame fr_Priority:

Name         Value
==========   =====
priority_y   Yes
priority_n   No

So if the user selects priority_y then Yes is inserted into the Excel spreadsheet.

The third set of items I want to insert comes from three drop downs. This is fairly straightforward, but the user is required to make a selection in all three drop downs. If they are not interested in a preference then they select 'No Preference'. If the user makes this decision then nothing should be inserted into cell. The following names/values are present:

Name         
==========   
cbo_fac1   
cbo_fac2
cbo_fac3

For instance if the user makes a selection of 111,222,No Preference in cbo_fac1, cbo_fac2, cbo_fac3 then only 111,222 gets inserted. If 111,No Preference,No Preference is selected then only 111 gets inserted.

This is the code I am using right now:

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, " ")

rng1.Offset(1, 0) = deptCodeSplit(0)
rng1.Offset(1, 1) = cbo_moduleCode.Value
rng1.Offset(1, 2) = cbo_moduleName.Value
rng1.Offset(1, 3) = txt_studentNo.Value
rng1.Offset(1, 4) = cbo_day.Value
rng1.Offset(1, 5) = cbo_period.Value
' rng1.Offset(1, 6) = weeks
rng1.Offset(1, 7) = cbo_weeks.Value
rng1.Offset(1, 8) = cbo_semester.Value
rng1.Offset(1, 9) = cbo_rounds.Value
rng1.Offset(1, 10) = cbo_priority.Value
' rng1.Offset(1, 11) = lectureStyle
rng1.Offset(1, 12) = txt_noRooms.Value
rng1.Offset(1, 13) = cbo_park.Value
' rng1.Offset(1, 14) = fac
' rng1.Offset(1, 15) = pref
rng1.Offset(1, 16) = txt_specialReq.Value

End Sub

Thanks so much in advance!

David Venegoni · Accepted Answer · 2012-10-29 03:15:24Z

1

Some clarification on what exactly you are having trouble with would help me give you a better answer. What it sounds like is that you are trying to fill a range that is unknown in size as a user can choose up to 15 items in the first set, 1 in the second set and up to three in the third set. So, the first thing I would make some arrays to hold the items chosen by the user in a way like..

      Dim group1 as arraylist
      Dim group2 as arraylist
      Dim group3 as arraylist

      ' Go through checkboxes and add values if they are checked
      if checkbox1.checked = true then
       group1.add(1)
      end if ' do that for each checkbox or if you have a checklistbox do 

      for each thing in checklistbox1
        if thing.checked=true then
        group1.add(checklistbox1.indexof(thing)-1)
        end if
        next

Do the same type of procedure for group 2 and group 3

        if not dropdown1.selecteditem = "no preference" then
          group3.add(...)
         end if ' so an and so forth

Now, you can add group1, group2 and group3 to the sheet like

         Dim StartingRange as range = ws.Cells(Rows.Count, "a").End(xlUp)
         Dim total_items as integer = (group1.items.count + group2.items.count + group3.items.count)

I don't know which way you want to add items, in the row or in the columns but you can size the startingrange by...

           startrange.resize(totalitems, 1) ' resizes range to 1 column and total items in rows

then

           Dim cell as variant

          for each cell in startrange
            cell.value = group1.item(0)
            group1.removeat(0)   ' this effectively moves the item at index 1, to index 0
             next

do for rest of groups

if that doesn't help let me know. My VBA is a little bit rusty, so I may have missed a few things in the code like 'Set' ranges, etc, so I do apologize for that.

answered Oct 29, 2012 at 3:15

David Venegoni

5083 silver badges14 bronze badges

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

4 Comments

methuselah Over a year ago

I want to add items in the row

David Venegoni Over a year ago

Then instead of resize(totalitems, 1), do resize (1, totalitems)

methuselah Over a year ago

' rng1.Offset(1, 6) = weeks is the code segment which refers to the cell in which all the weeks have to go. How do I insert them into that cell?

David Venegoni Over a year ago

I'm kind of confused to what you are actually placing in the cell, but I would try rng1.Offset(1,6).value = "whatever you want" or if you are saying you want the value that is in rng1.offset(1,6) to be inserted into another cell, then rng1.range("thecellyouwant").value = rng1.offset(1,6).value, let me know if I am not understanding the question correctly.

Collectives™ on Stack Overflow

Inserting checkbox, radio and dropdown values from VBA form into Excel spreadsheet

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related