Solved

excel form checkboxes

Posted on 2014-03-23
6
233 Views
Last Modified: 2014-03-24
hello all,

is there a faster way in vba to add multiple check-boxes in a form in excel? the checkbox names could be checkbox1,checkbox2,checkbox3.....etc

i have create 35 checkboxes within a form and then do the same for other forms as well...so this is kind of pain

Thank you
0
Comment
Question by:Rayne
  • 4
  • 2
6 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
Comment Utility
Do you mean a userform or a form on a worksheet?

For a userform you could use something like this.
Private Sub UserForm_Initialize()
Dim chk As MSForms.Checkbox
Dim lngTop As Long

    lngTop = 5

    For I = 1 To 35
        Set chk = Me.Controls.Add("Forms.CheckBox.1, "CheckBox" & I)
        chk.Top = lngTop
        lngTop = lngTop+chk.Height+5
    Next I

End Sub

Open in new window

That puts all the checkboxes in one column which might not be suitable but the code can be refined to place them in some other configuration.

Similar code can be created if the form is on a sheet.

By the way, you could use a listbox with MultiSelect set to fmMultiSelectMulti and ListStyle set to fmListStyleOption.

That would show a chekcbox next to each item in the listbox.
0
 

Author Comment

by:Rayne
Comment Utility
Thank you Imnorie, that is awesome
how can use the listbox - how can i populate the list box with data set from one column in worksheet - please assist
0
 

Author Comment

by:Rayne
Comment Utility
so the question is > how do i add a set of vlaues in a worksheet to that awesome list box you mentioned? I am not able to populate the listbox so far..

Thank you
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!

 

Author Comment

by:Rayne
Comment Utility
you are awesome imnorie, you saved me a huge chunk of time - listbox rocks, checkboxes not.....thank you thank you , i got it :)
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
I take it you figured out the listbox?

If you didn't here's a simple example that populates a listbox from A2:A35 on Sheet1 and sets it up to display checkboxes.
Private Sub UserForm_Initialize()

    With Me.ListBox1
        .List = Sheets("Sheet1").Range("A2:A35").Value
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
    End With

End Sub

Open in new window

0
 

Author Comment

by:Rayne
Comment Utility
Thank you Imnorie,

This is Perfection to the ULTIMATE, thank you, thank you :)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

744 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