Solved

How to create dynamic check boxes in VBA

Posted on 2014-03-26
8
567 Views
Last Modified: 2014-04-06
I have a userform that will contain text strings.  The text strings will be kept in a separate document so the strings can be added to, deleted or modified.

I won't know how many of them there will be or how many the user will want to choose, so I want to create a check box with one of the strings next to it, and continue that until all text strings and check boxes have been created.

That will allow the user to choose as many text strings as they need.

What I need is the code to accomplish the creation of the text boxes with the associated strings

Thanks for the help
0
Comment
Question by:c7c4c7
  • 4
  • 2
  • 2
8 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 39958200
The scenario wasn't too precise but to demonstrate I have made some assumptions:
The strings documdent might be open but if not open it for the purpose
The strings document is a defined document i.e. not necessarily the active one
The document containing the macro is the current one
The strings are entered as paragraphs

On this assumption then try:

Sub Q_28398555()
Dim doc As Document
Dim bolFound As Boolean
Dim arrStrings As Variant
Dim frm As UserForm
Dim intCount As Integer
Const strDocName = "Q_28398555.docx"
Const strdocPath = "C:\"

    bolFound = False
    For Each doc In Application.Documents
        If LCase(doc.Name) = LCase(strDocName) Then
            bolFound = True
            Exit For
        End If
        Set doc = Nothing
    Next
    If Not bolFound Then Set doc = Application.Documents.Open(strdocPath & strDocName)
    arrStrings = Split(doc.Range.Text, vbCr)
    Load UserForm1
        For intCount = LBound(arrStrings) To UBound(arrStrings)
            If arrStrings(intCount) <> "" Then
                With UserForm1.Controls.Add("Forms.CheckBox.1", "Checkbox_" & intCount, True)
                    .Top = 20 * intCount
                    .Caption = arrStrings(intCount)
                End With
            End If
        Next
    UserForm1.Show
End Sub

Open in new window


Chris
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 39958209
Hi c7c4c7 - Rename zip to dotm.
UserFormExample--II.zip
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 39958217
Hi Chris,

Hope you're well. You must have posted just as I started :-)   I think this question relates to an earlier question.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39958231
Hi yourself, I have seen you around a few times but Feb/March tends to be a quiet period for me.

I take your point that it was an ongoing question but there was no mention to this in the question and as a passing comment I understood the number of lines of text was an unknown that will vary in use therefore your solution looks (to my interpretation) as though it doesn't quite hit the spot ;o)

Chris
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 14

Expert Comment

by:DrTribos
ID: 39958248
I only mentioned it to give some BG info, I agree with your assessment - mine is a bit to the left of center.

With clarificaiton from the OP I'm thinking a userform could work if it is driven by a macro that loops through the rows in a table. Or tables in a document.

Just providing a starting spot.

Cheers,
0
 

Author Comment

by:c7c4c7
ID: 39959265
I haven't had a chance to work with the responses.  Dr. Tribos is correct it is an extension of the previous question.  

I won't have time to work on this until later today but thanks for the posts
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 39960273
No worries - let us know how you go
0
 

Author Closing Comment

by:c7c4c7
ID: 39981413
Thanks for the help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Like many others, we try and discourage users from printing documents unnecessarily and instead send or share them electronically. However, this doesn't always work and documents are still printed. With this simple solution, if the user tries to …
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

911 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

23 Experts available now in Live!

Get 1:1 Help Now