Solved

Using data from a dynamic vba form in Excel

Posted on 2013-01-16
9
400 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 46

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 46

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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 46

Expert Comment

by:Martin Liss
ID: 38783274
Never mind I understand mycontrol 13.
0
 
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 46

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 46

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 46

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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 …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

860 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