Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Grouping changes in VBA

    Hi Experts,

    I am trying to learn how to be more efficient in coding. I would like to make changes tin txt boxes 3, 4,and 5, as I did in txt2, without duplicating the code below multiple times.

    Any ideas? Thanks in advance for your help.



    Private Sub checkbox2_click()
    If CheckBox2 Then
    Txt2.Visible = False
    Else
    Txt2.Visible = True
    End If
    End Sub
    Last edited by Excelnewbie; 2015-11-28 at 18:31.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    Try this:
    Code:
    Private Sub checkbox2_click()
       Txt2.visible = Not CheckBox2
    End Sub
    Here's a test file: EN-HideShowTxtBox.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG-

    Wonderful. Less code, does the same job, therefore more efficient.

    Question 1: If I have 5 check boxes and 5 text boxes I want to do the same thing with, what is the best way to accomplish that? Just write the same code 5 different times?

    Question2: If I want to make 4 Text boxes invisible by checking 1 Checkbox. How would the code look?
    Last edited by Excelnewbie; 2015-11-28 at 18:36.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    Since you need the Click Event for each of the Check Boxes and it's only a one liner that's the best method.

    Now if you had a lot of code that was duplicated for the Click Event for each box then we could look into writing a procedure or function that could be called with arguments pointing the the appropriate checkbox number. But, that's a whole nother can of worms.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Excel,

    Here is the other approach RG was pointing out. If these are controls on a userform you could repeat the following code for each checkbox#_Click event that calls only on common routine. You can do the same thing if the controls are on a worksheet instead of a form.

    HTH,
    Maud

    In the form module:
    Code:
    Private Sub CheckBox1_Click()
        tb 1  'CHANGE THE NUMBER ACCORDING TO THE CONTROL
    End Sub
    In a standard module:
    Code:
    Public Sub tb(x As Integer)
        With UserForm1
            .Controls("TextBox" & x).Visible = .Controls("Checkbox" & x)
        End With
    End Sub

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    RetiredGeek (2015-11-28)

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    "To be or NOT to be, that is the question!" I think you forgot the NOT in your code.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Yes, I now see that Excel wants the visible property = False when the checkbox is ticked. Nice pickup!

    Code:
         .Controls("TextBox" & x).Visible = Not .Controls("Checkbox" & x)
    Thx,
    Maud

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    BTW: I forgot to mention that the code you said to put in a standard module will also work in the Form module which is probably a more logical place for it to keep it associated with the form. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    RG,

    That may be true but I guess I was looking at it from the point of view that there may be more than just one form.

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Good Point. In that case you'd need to modify the code to:
    Code:
    Public Sub ShowHideTB(ufValue As UserForm, iBoxNo As Integer)
    
        With ufValue
            .Controls("TextBox" & iBoxNo).Visible = Not .Controls("Checkbox" & iBoxNo)
        End With
        
    End Sub
    And call it with:
    Code:
    Private Sub CheckBox1_Click()
    
       ShowHideTB Me, 1
       
    End Sub
    Test File: EN-HideShowTxtBox.xlsm

    HTH
    Last edited by RetiredGeek; 2015-11-29 at 10:58.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2015-11-29)

  13. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Nicely Done!

  14. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you have a lot of controls that do basically the same thing, I'd suggest using a class containing a checkbox variable declared WithEvents, and a textbox variable.

    Revised version of RG's first file attached.
    Attached Files Attached Files
    Last edited by rory; 2015-12-01 at 08:25.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. The Following 2 Users Say Thank You to rory For This Useful Post:

    Anklebuster (2015-12-03),RetiredGeek (2015-12-01)

  16. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Very Cool! Thanks!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  17. #14
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Thanks to RG, Maud, and Rory!

    This is very helpful. I am learning a great deal thanks to each of you.

    I'm grateful.

  18. #15
    New Lounger
    Join Date
    Dec 2009
    Location
    Gordonsville, VA
    Posts
    17
    Thanks
    4
    Thanked 2 Times in 2 Posts
    Rory, this is intriguing. Where can I learn more about WithEvents and, specifically, your implementation in the class handler? (Translation: I am clueless...)

    [edited to eradicate cluelessness]

    I wanted to figure out why the collection object was used. I found the answer in StackOverflow, along with a generally useful tutorial for event handlers in Excel VBA:
    Note that the reason you need to add the event handler instances to a collection is simply to ensure that they remain referenced and thus don't get discarded by the garbage collector before you're finished with them.

    Cheers,

    Mitch
    Last edited by Anklebuster; 2015-12-03 at 11:47.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •