CheckBoxes vs. OptionButtons in VBA


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: CheckBoxes vs. OptionButtons in VBA

Hybrid View

  1. #1
    Join Date
    Feb 2013
    Posts
    5

    CheckBoxes vs. OptionButtons in VBA

    I have a UserForm in Excel with 100 checkboxes. At runtime, depending on which CommandButton a user clicks on, I get the contents of a particular column on one of 3 sheets and use those values for the Caption property of some (or all) of the CheckBoxes and hide the rest. My problem is that I also have 2 OptionButtons on the form, and my code for finding the Checkboxes and setting the Captions is not distinguishing between them and the CheckBoxes.
    Code:
        Dim i As Integer
        For Each ctl In Me.Controls
            If TypeOf ctl Is MSForms.CheckBox Then
                If i < intNumChoices Then
                    ctl.Caption = astrChoices(i)
                    ctl.Visible = True
                    i = i + 1
                Else
                    ctl.Visible = False
                End If
            End If
        Next
    (Please, no comments about the Hungarian notation. I still prefer it! :-)

    When I run this, the first two values in astrChoices() are put on the OptionButtons. How do I distinguish between the two different types of controls?

    Thanks.
    Last edited by Hack; 02-20-2013 at 02:46 PM. Reason: Added code tags

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Try moving the two option buttons into a picturebox control or a frame control and get them off of the form itself.

    (By the way, I have a comment on your Hungarian notation. You go right ahead and keep using it. I still do.....in I use it for naming my controls in VB.NET projects!)
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Feb 2013
    Posts
    5
    Thanks, but that didn't work. However, I solved it by using the name:

    If Left(ctl.Name, 8) = "CheckBox" Then

    Really strange!

    (Yes, I continued using Hungarian notation to the end of my 4-decades-long software development career (mostly VB.net at the end). And, even though I was chair of our Development Governance Council, I never could convince the younger members of what I perceived to be its advantages. Oh, well -- I'm free to use it all I want now! :-)

  4. #4
    Join Date
    Feb 2013
    Posts
    5
    Thanks, but that didn't work. However, I solved it by using the name:

    If Left(ctl.Name, 8) = "CheckBox" Then

    Really strange!

    (Yes, I continued using Hungarian notation to the end of my 4-decades-long software development career (mostly VB.net at the end). And, even though I was chair of our Development Governance Council, I never could convince the younger members of what I perceived to be its advantages. Oh, well -- I'm free to use it all I want now! :-)

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Yes, it is strange, and not something I can duplicate.

    I didn't put 100 checkboxes on a userform, but, for testing purposes, I did put 10 on one and then added a couple of option buttons. I had no difficulty interating through the checkbox group and ignoring the option buttons.

    On another note, I've encountered other instances where VBA code did not work as I would have expected so I'm not completely surprised that you needed to employ some creative coding.

    And you have me by an entire decade....I've only been at it for 3
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. get the value of checkboxes
    By smithsf22 in forum ASP.NET
    Replies: 1
    Last Post: 04-22-2004, 10:52 PM
  2. Access grouped optionbuttons by index
    By Jürgen Schoch in forum VB Classic
    Replies: 2
    Last Post: 11-20-2002, 02:00 PM
  3. checkboxes
    By Brian Pittman in forum ASP.NET
    Replies: 2
    Last Post: 10-19-2001, 08:21 AM
  4. Checkboxes
    By Matt Wyant in forum VB Classic
    Replies: 1
    Last Post: 10-22-2000, 09:35 AM
  5. CheckBoxes
    By Chris in forum VB Classic
    Replies: 2
    Last Post: 07-18-2000, 06:04 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center