Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Excel Userform - Multiple Checkbox comments populate textbox upon clicking

  1. #1
    New Member
    Join Date
    Feb 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel Userform - Multiple Checkbox comments populate textbox upon clicking

    Hello! I have a userform with two sections. There are 4 checkboxes in each section with the idea that they will be pre-populated comments that will fill into a textbox. I am able to get it to populate text into the textbox when one box is checked however the way I have it written only allows for one comment to be populated at a time. I need to plan for a scenario where all 4 boxes plus some free hand comments could be added to the textbox. I am sure this can be done but I am struggling to find the answer.

    Also - I have 2 sections and 2 textboxes. So the checkboxes need to populate the textbox that they are related to...

    Here is what I have so far:

    This is the code for one of the checkboxes (this works and is identical for each box but as stated, only allows one to be clicked)
    [Private Sub CBParty_Click()
    If Me.CBParty.Value = True Then
    TXTPayment = "Payment sent to an incorrect party."
    Else
    TXTPayment = ""
    End If
    End Sub]

    This is what I was trying to do to loop through but I know its not right..


    [Private Sub CBChecker_()
    Dim c As Control
    For Each c In Me.Controls
    If TypeName(c) = "CheckBox" Then
    If c.Value = True Then
    TXTPayment.Value
    End If
    End If

    End Sub]

    Thank you in advance for any help you can provide!

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Excel Userform - Multiple Checkbox comments populate textbox upon clicking

    So if I understand correctly.

    You want to click on one checkbox which will result in entering the same value into a section of textboxes.

    The key word here is section.

    Excel has no such thing as a section.

    You could put all your Textboxes into what is know as a Frame.

    Then we would tell the script when you click on Checkbox named Checkbox1 to enter "Alpha" into all the Textboxes in Frame named Frame1

    And then if you were to click on Check box name CheckBox2 to enter "Bravo" into all Textboxes in Frame named Frame2

    Would this work for you.

    If so give me the name of the two Checkboxes and the name of the two frames.

    Look in the Vba Toolbox and you will see the Tool named Frame.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Userform - Multiple Checkbox comments populate textbox upon clicking

    Quote Originally Posted by My Aswer Is This View Post
    So if I understand correctly.

    You want to click on one checkbox which will result in entering the same value into a section of textboxes.

    The key word here is section.

    Excel has no such thing as a section.

    You could put all your Textboxes into what is know as a Frame.

    Then we would tell the script when you click on Checkbox named Checkbox1 to enter "Alpha" into all the Textboxes in Frame named Frame1

    And then if you were to click on Check box name CheckBox2 to enter "Bravo" into all Textboxes in Frame named Frame2

    Would this work for you.

    If so give me the name of the two Checkboxes and the name of the two frames.

    Look in the Vba Toolbox and you will see the Tool named Frame.

    Actually, "sections" do exist in the form of control arrays. They do not work the same as in other flavors of VB, but they are there and they can be very useful

  4. #4
    New Member
    Join Date
    Feb 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Userform - Multiple Checkbox comments populate textbox upon clicking

    So there are 2 text boxes. 1 textbox would need to collect the "comments" from 4 checkboxes. Then the other textbox would need to collect the comments from the other 4 checkboxes.

    Checkbox Names (when clicked a comment needs to populate into the textbox- TXTPayment) Right now, I can click one of the boxes and it will populate the txtbox but if I want more than one of the checkbox comments to populate it wont do it.
    CBParty
    CBAmount
    CBExposure
    CBType

    Second set of Checkboxes to populate a comment into textbox - TXTReserve
    CBExposure
    CBGuidelines
    CB3
    CB4

    I hope that makes a little more sense and thank you so much for your help!

  5. #5
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Userform - Multiple Checkbox comments populate textbox upon clicking

    Quote Originally Posted by jbeet View Post
    So there are 2 text boxes. 1 textbox would need to collect the "comments" from 4 checkboxes. Then the other textbox would need to collect the comments from the other 4 checkboxes.

    Checkbox Names (when clicked a comment needs to populate into the textbox- TXTPayment) Right now, I can click one of the boxes and it will populate the txtbox but if I want more than one of the checkbox comments to populate it wont do it.
    CBParty
    CBAmount
    CBExposure
    CBType

    Second set of Checkboxes to populate a comment into textbox - TXTReserve
    CBExposure
    CBGuidelines
    CB3
    CB4

    I hope that makes a little more sense and thank you so much for your help!

    Let me see if I am following...

    You have a text box with 4 checkboxes 'linked' to it, so to speak. hypothetically, those checkbox values will be 1, 2, 3, 4 for the purposes of this question...


    If you click checkbox 1 and 3, you want the textbox to read "13"

    if you click checkbox 1, 3, and 4, you want the textbox to read "134"

    Is this correct>?

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Excel Userform - Multiple Checkbox comments populate textbox upon clicking

    You said:
    So there are 2 text boxes. 1 textbox would need to collect the "comments" from 4 checkboxes. Then the other textbox would need to collect the comments from the other 4 checkboxes.

    Check Boxes do not normally have comments

    Check Boxes have true or false Values and have Caption like "CheckBox1"

    What is a Check Box comment??


    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Excel Userform - Multiple Checkbox comments populate textbox upon clicking

    What is a CheckBox Value ??

    Quote Originally Posted by Steve_ View Post
    Let me see if I am following...

    You have a text box with 4 checkboxes 'linked' to it, so to speak. hypothetically, those checkbox values will be 1, 2, 3, 4 for the purposes of this question...


    If you click checkbox 1 and 3, you want the textbox to read "13"

    if you click checkbox 1, 3, and 4, you want the textbox to read "134"

    Is this correct>?
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  8. #8
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Userform - Multiple Checkbox comments populate textbox upon clicking

    Quote Originally Posted by My Aswer Is This View Post
    What is a CheckBox Value ??

    It is a checkbox. It has two values. 0 and 1.
    Or True and False.

    I assume he is trying to use the checkbox.caption value though.

    Nitpicking at terminology will likely not answer his question tho.
    Last edited by Steve_; Jul 18th, 2019 at 05:57 PM.

  9. #9
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Userform - Multiple Checkbox comments populate textbox upon clicking

    Quote Originally Posted by jbeet View Post
    So there are 2 text boxes. 1 textbox would need to collect the "comments" from 4 checkboxes. Then the other textbox would need to collect the comments from the other 4 checkboxes.

    Checkbox Names (when clicked a comment needs to populate into the textbox- TXTPayment) Right now, I can click one of the boxes and it will populate the txtbox but if I want more than one of the checkbox comments to populate it wont do it.
    CBParty
    CBAmount
    CBExposure
    CBType

    Second set of Checkboxes to populate a comment into textbox - TXTReserve
    CBExposure
    CBGuidelines
    CB3
    CB4

    I hope that makes a little more sense and thank you so much for your help!
    This is a REAL stretch...but I am trying. Is this what you have in mind?


    Code:
    Private Sub chkCBParty_Click()
        txtStr_TXTPayment
    End Sub
    Private Sub chkCBAmount_Click()
        txtStr_TXTPayment
    End Sub
    Private Sub chkCBExposure_Click()
        txtStr_TXTPayment
    End Sub
    Private Sub chkCBType_Click()
        txtStr_TXTPayment
    End Sub
    Function txtStr_TXTPayment()
        If Me.chkCBParty Then
            chkStr = chkStr & Me.chkCBParty.Caption
        End If
        If Me.chkCBAmount Then
            chkStr = chkStr & Me.chkCBAmount.Caption
        End If
        If Me.chkCBExposure Then
            chkStr = chkStr & Me.chkCBExposure.Caption
        End If
        If Me.chkCBType Then
            chkStr = chkStr & Me.chkCBType.Caption
        End If
        Me.txtPayment = chkStr
    End Function
    Private Sub chkCBExposure2_Click()
        txtStr_TXTReserve
    End Sub
    Private Sub chkCBGuidelines_Click()
        txtStr_TXTReserve
    End Sub
    Private Sub chkCB3_Click()
        txtStr_TXTReserve
    End Sub
    Private Sub chkCB4_Click()
        txtStr_TXTReserve
    End Sub
    Function txtStr_TXTReserve()
        If Me.chkCBExposure2 Then
            chkStr = chkStr & Me.chkCBExposure2.Caption
        End If
        If Me.chkCBGuidelines Then
            chkStr = chkStr & Me.chkCBGuidelines.Caption
        End If
        If Me.chkCB3 Then
            chkStr = chkStr & Me.chkCB3.Caption
        End If
        If Me.chkCB4 Then
            chkStr = chkStr & Me.chkCB4.Caption
        End If
        Me.txtReserve = chkStr
    End Function

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    Mťxico
    Posts
    6,264
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Excel Userform - Multiple Checkbox comments populate textbox upon clicking

    I guess you need something like this.




    -------------
    Create a class module and put the following.
    Change UserForm1 to the name of your userform

    Code:
    Public WithEvents chkB As MSForms.CheckBox 'Custom checkbox
    Private Sub chkB_Click()
        Call UserForm1.check_groups()
    End Sub


    -------------------------
    In the userform code:
    Code:
    Dim colCheck As Collection 'To the top of all the code
    
    
    Private Sub UserForm_Initialize()
        Dim ctlLoop As MSForms.Control, clsObject As Class1
        
        Set colCheck = New Collection
        For Each ctlLoop In Me.Controls
            If TypeName(ctlLoop) = "CheckBox" Then
                Set clsObject = New Class1
                Set clsObject.chkB = ctlLoop
                colCheck.Add clsObject
            End If
        Next ctlLoop
        txtPayment.MultiLine = True       'Textbox accept multiple rows
        TXTReserve.MultiLine = True
    End Sub
    
    
    Sub check_groups()
        txtPayment = ""
        TXTReserve = ""
        If chkCBParty Then txtPayment = "Payment sent to an incorrect party." & Chr(13)
        If chkCBAmount Then txtPayment = txtPayment & "Message 2 chkCBAmount" & Chr(13)
        If CBExposure Then txtPayment = txtPayment & "Message 3 CBExposure" & Chr(13)
        If CBType Then txtPayment = txtPayment & "Message 4 CBType" & Chr(13)
    
    
        If CBExposure2 Then TXTReserve = "Message1 CBExposure2." & Chr(13)
        If CBGuidelines Then TXTReserve = TXTReserve & "Message 2 CBGuidelines" & Chr(13)
        If CB3 Then TXTReserve = TXTReserve & "Message 3 CB3" & Chr(13)
        If CB4 Then TXTReserve = TXTReserve & "Message 4 CB4" & Chr(13)
    End Sub
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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