Solved

Using data from a dynamic vba form in Excel

Posted on 2013-01-16
9
375 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
Question by:Derek Schauland
  • 6
  • 3
9 Comments
 
LVL 45

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

Author Comment

by:Derek Schauland
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 45

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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38783274
Never mind I understand mycontrol 13.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Author Comment

by:Derek Schauland
ID: 38783323
That whole section can be tweaked as needed... was trying to find out how to access dynamically generated controls
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 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 45

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
ID: 38783507
This is awesome. Thanks so much for your help
0
 
LVL 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now