VB macro to copy checked cells into a textbox

[Mady84]

Hello there!

i need help with a macro to copy cells (the text in them) when the corresponding checkbox (linked with them) is checked into a textbox in the same sheet..

and i would be great if the text is removed from the textbox when i uncheck the corresponding checkbox! if its too complicated then its ok, i will have to deleted manually!

for example:
i have text in column B and checkboxes in A, if checkboxes A2, A3 and A49 are checked then the text in B2, B3 and B49 get copied in the textbox..

this would help me a lot in my job, so i appreciate the help very much

thanks in advance

 

[NoSparks]

More info please.

What kind of checkboxes, are they from the Form Controls or ActiveX Controls portion of Insert Controls from the ribbon ?

What is typical text in column B, single words or sentences ?

Is the text in the textbox to be in the order it appears in column B or in the order in which the checkboxes are checked ?

How are you using the resulting text from the textbox ?

 

[Mady84]

What kind of checkboxes, are they from the Form Controls or ActiveX Controls portion of Insert Controls from the ribbon ?

First of all, thanks for responding
Here it doesn´t really matter, whatever easier in programming and in use later.

What is typical text in column B, single words or sentences ?

it would be sentences, usually more than 1.

Is the text in the textbox to be in the order it appears in column B or in the order in which the checkboxes are checked ?

in the order of appearance as in column B.

How are you using the resulting text from the textbox ?

The resulting text will be copied and pasted in another program, that looks like word..

It would be something like this

This COPY button should initiate another macro that copies the content of the textbox to clipboard, to be pasted in the other program.

Looking forward to your reply, and thank you again

 

[NoSparks]

OK, here's what I've come up with.

Have assumed that column B is already populated with your sentences, and that the TextBox and Copy button are already on the sheet.
Ran this macro to put Form Checkboxes in column A
Should only need to run it once.

 Private Sub Insert_Form_Checkboxes()
     Dim myCell As Range, myRng As Range
     Dim CBX As CheckBox, Lrow As Long

With ActiveSheet
    'delete ALL existing checkboxes from sheet, links are NOT cleared.
    .CheckBoxes.Delete  '<~~~~~ comment out to NOT delete existing checkboxes
    
    'last row in B
    Lrow = .Range("B" & Rows.Count).End(xlUp).Row
    'range of chkboxes
    Set myRng = .Range("A2:A" & Lrow)    '<~~~~~ enter the range to have checkboxes
    .Columns(1).ColumnWidth = 6
End With

Application.ScreenUpdating = False
    For Each myCell In myRng.Cells
        If myCell.Offset(, 1) <> "" Then
            With myCell
                Set CBX = .Parent.CheckBoxes.Add _
                            (Top:=.Top, Left:=.Left + .Width / 4, _
                             Width:=.Width / 5, Height:=.Height) 'click area size
                CBX.Name = "CBX_" & .Address(0, 0)
                CBX.Caption = ""         'whatever you want, "" for none
                CBX.Value = xlOff                       'initial value unchecked
                CBX.LinkedCell = .Offset(0, 0).Address '<~~~~~ offset to linked cell
                CBX.OnAction = "ChkBox_Action"    'macro called each time clicked
            End With
        End If
    Next myCell
Application.ScreenUpdating = True
End Sub

Every time a checkbox is clicked this "ChkBox_Action" macro will be run

Sub ChkBox_Action()
    Dim rng As Range, cel As Range
    Dim lr As Long
    Dim stxt As String
    Dim oObj As OLEObject
    
stxt = ""  'start blank

With ActiveSheet
    lr = .Range("B" & Rows.Count).End(xlUp).Row     'last row
    Set rng = .Range("A2:A" & lr)                   'range to work on
    For Each cel In rng
        If cel = True Then
            stxt = stxt & " " & cel.Offset(, 1).Value
        End If
    Next cel
    stxt = Mid(stxt, 2)     'remove leading space
    ' write to the text box
    Set oObj = .OLEObjects("TextBox1")
        With oObj.Object
            .Value = stxt
        End With
End With
End Sub

This macro assigned to the Copy button will copy the text to the clipboard

Sub TheCopyButton()
    Dim myClipboard As New DataObject
If ActiveSheet.TextBox1.Value <> "" Then
    myClipboard.SetText ActiveSheet.TextBox1.Value
    myClipboard.PutInClipboard
    MsgBox "TextBox copied to Clipboard"
Else
    MsgBox "Nothing in TextBox to copy"
End If
End Sub

You could also add another button to clear the text box and reset the checkboxes by assigning this macro.

Sub TheClearButton()
    Dim lr As Long
With ActiveSheet
    lr = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("A2:A" & lr).ClearContents
    .TextBox1.Value = ""
End With
End Sub

For your examination here's a link to my test file

Hope that helps, good luck with your project.

 

[Mady84]

Thank you very much NoSparks

That was fast, and you made it seem very easy!!

However considering i almost have no experience with VBA, i can´t get it to work!! ?.

On the other hand, your test file works perfectly!

So i think it would really help me if you make it work until the 150th row, and you don't divide them into sections. Maybe write just numbers from 1-150 in cells of column B, and ill replace the numbers with my sentences ?, unless its too much trouble for you ??

 

[NoSparks]

However considering i almost have no experience with VBA, i can´t get it to work!! ?.

On the other hand, your test file works perfectly!

So i think it would really help me if you make it work until the 150th row, and you don't divide them into sections. Maybe write just numbers from 1-150 in cells of column B, and ill replace the numbers with my sentences ?, unless its too much trouble for you ?

too much trouble... so easy I'm sure you can do it

Paste whatever you want into column B.
Click the Clear button. (this removes any True or False from column A)

Alt + F11 will bring up the VBA environment.
Double click on the Insert_ChkBoxes module and you'll see the Insert_Form_Checkboxes procedure.
Place the cursor anywhere within that procedure.
Press the F5 key.
Done.
Play with it and see.

 

[Mady84]

It worked!!!!!? you are right, it was unbelievably easy.. well only after to told me how ?
But you really just made my year !!! ?
I don't know how to thank you!! ?

 

[NoSparks]

That's great, glad to have helped and thanks for the feedback.