Excel Userform - Multiple Checkbox comments populate textbox upon clicking

jbeet

New Member
Joined
Feb 1, 2015
Messages
7
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!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,728
Office Version
2013
Platform
Windows
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.
 

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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
 

jbeet

New Member
Joined
Feb 1, 2015
Messages
7
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!
 

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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>?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,728
Office Version
2013
Platform
Windows
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??

<strike>
</strike>
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,728
Office Version
2013
Platform
Windows
What is a CheckBox Value ??

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>?
 

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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:

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,221
Office Version
2007
Platform
Windows
I guess you need something like this.




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

Code:
[/B]Public WithEvents chkB As MSForms.CheckBox 'Custom checkbox
Private Sub chkB_Click()
    Call [COLOR=#ff0000]UserForm1[/COLOR].check_groups()
End Sub
[B]
-------------------------
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
[COLOR=#0000ff]    txtPayment.MultiLine = True       '[/COLOR]Textbox accept multiple rows
[COLOR=#0000ff]    TXTReserve.MultiLine = True[/COLOR]
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
 

Watch MrExcel Video

Forum statistics

Threads
1,089,868
Messages
5,410,884
Members
403,333
Latest member
SH2020

This Week's Hot Topics

Top