Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using data from a dynamic vba form in Excel

Posted on 2013-01-16
9
Medium Priority
?
413 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

598 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