?
Solved

Using data from a dynamic vba form in Excel

Posted on 2013-01-16
9
Medium Priority
?
410 Views
Last Modified: 2013-01-16
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
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38783197
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
 
LVL 9
ID: 38783223
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38783263
You should always have Option Explicit at the top of your userform's code.

What/where is mycontrol3?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 49

Expert Comment

by:Martin Liss
ID: 38783274
Never mind I understand mycontrol 13.
0
 
LVL 9
ID: 38783323
That whole section can be tweaked as needed... was trying to find out how to access dynamically generated controls
0
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 38783444
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38783461
If you want to accumulate the votes, change line 12 to Cells(r, 2).Value = Cells(r, 2).Value + 1
0
 
LVL 9

Author Closing Comment

by:Derek Schauland (Microsoft MVP)
ID: 38783507
This is awesome. Thanks so much for your help
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38783556
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

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question