Using data from a dynamic vba form in Excel

I have created a dynamic form in Excel vba to loop through a column and add checkboxes with the column items as captions on the form.

My goal is to use the form as a voting form where a user would check the boxes for the choices needed and click the submit button, then for each box checked, the column in Excel would be updated +1 to count total votes for a selection.

I cannot get the submit and count of votes for checked boxes to work at all.  Any assistance provided would be greatly appreciated

Derek
LVL 9
Derek Schauland (Microsoft MVP)IT ConsultantAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Martin LissConnect With a Mentor Older than dirtCommented:
Private Sub btnVote_Click()

'If Me.mycontrol3.Value = True Then

    Dim lngIndex As Long
    Dim ctl As Control
    Dim r As Integer
    For Each ctl In Controls
        If InStr(ctl.Name, "mycontrol") > 0 Then
            If ctl.Value = True Then
                r = VBA.Mid(ctl.Name, 10)
                Cells(r, 2).Value = "1"
            End If
        End If
'    Range("B2").Select
'    For Each cell In Selection
'        cell = cell + 1
'    Next cell
    Next

End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
Can you attach your workbook or show the code you are using to add the controls and the code you use to add up the checks?
0
 
Derek Schauland (Microsoft MVP)IT ConsultantAuthor Commented:
Absolutely

the workbook is attached.  On the first page, there is a button to launch the form, on the second the list of captions and results fields are in columns a and b.
votesxlsx.xlsm
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Martin LissOlder than dirtCommented:
You should always have Option Explicit at the top of your userform's code.

What/where is mycontrol3?
0
 
Martin LissOlder than dirtCommented:
Never mind I understand mycontrol 13.
0
 
Derek Schauland (Microsoft MVP)IT ConsultantAuthor Commented:
That whole section can be tweaked as needed... was trying to find out how to access dynamically generated controls
0
 
Martin LissOlder than dirtCommented:
If you want to accumulate the votes, change line 12 to Cells(r, 2).Value = Cells(r, 2).Value + 1
0
 
Derek Schauland (Microsoft MVP)IT ConsultantAuthor Commented:
This is awesome. Thanks so much for your help
0
 
Martin LissOlder than dirtCommented:
For line 9 you can also do something like

If TypeOf ctl Is msforms.CheckBox Then

In any case you're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.