• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code needed for copying checkbox captions to clipboard

Grumpy88

Grumpy88

Member
Hi all.

I have create a UserForm with 47 checkboxes on it, each obviously with a different caption, as well as a textbox for user free text entry. The requirement is that a user enters text (a policy number) into the text box, selects however many of the checkboxes apply to it and then clicks a command button to copy both the entered policy number and the captions of the selected checkboxes to the clipboard, for subsequent pasting into another application. The idea is to save on typing the applicanle checkbox captions manually into the other application, as some of them are nine - ten words long.
 
Sorry, I guess it'd be far easier if I posted a link to the actual spreadsheet so that you can see what I'm talking about (and hopefully practice proposed solutions on ;))! Will upload and send a link later.
 
Hi Grumpy88,

Try the below code. This will let you grab the string data and puts it on the clipboard.

Remember, you will have to add a reference to your VBA project, go to the Tools menu in the VBA editor and choose the References item. In the dialog that appears, scroll down the list and choose Microsoft Forms 2.0 Object library.

Let me know, how this goes. Thanks.

Code: 
Private Sub CommandButton1_Click()
Dim Objclipboard As New MSForms.DataObject
Dim strclipboard As String


strclipboard = Txtuserfree.Text & Chr(10)

If CheckBox1.Value Then strclipboard = strclipboard & CheckBox1.Caption & Chr(10)
If CheckBox2.Value Then strclipboard = strclipboard & CheckBox2.Caption & Chr(10)

'....Add all your checkboxes here and concatenate the text

Objclipboard.SetText strclipboard
Objclipboard.PutInClipboard

End Sub
 
Thank you so much!

The debugger seems to have an issue with the "strclipboard = Txtuserfree.Text & Chr(10)" line when I run it though - am I supposed to customise this line somehow, or just paste it in verbatim?

Also what do you mean by "concatenate the text"? Can I not just copy your first two checkbox lines over and over, just changing the checkbox number each time?
 
Hey,

Replace Txtuserfree with your own text box name what you have on the form. Also, ignore that "concatenate the text" term here as I was asking you to add all your checkboxes in the way I have done these lines
Code: 
If CheckBox1.Value Then strclipboard = strclipboard & CheckBox1.Caption & Chr(10)
If CheckBox2.Value Then strclipboard = strclipboard & CheckBox2.Caption & Chr(10)

Continue the same line of code for each of your checkboxes.
 
You guys are so beyond awesome it's not even true!! That works exactly as I require it to. Thanks beyond words!!

Narayan, I don't know how to code the loop for CheckBox1 through to CheckBox48, so if you are willing to provide that in the interests of efficiency it would be terrific. In the interim though, 48 lines of almost identical code will also do the job.
 
Hi ,

Try this :
Code: 
Public Sub DisplayCheckboxesCaptions()
'          The following 4 lines of code will work with Form control checkboxes
          With ActiveSheet
                For i = 1 To .CheckBoxes.Count
                    If .CheckBoxes(i).Value = Checked Then MsgBox .CheckBoxes(i).Caption
                Next
          End With
         
'          The following 4 lines of code will work with ActiveX control checkboxes
          With ActiveSheet
                For i = 1 To .OLEObjects.Count
                    With .OLEObjects(i)
                        If .Name Like "CheckBox" & "*" Then If .Object.Value = True Then MsgBox .Object.Caption
                    End With
                Next
          End With
End Sub
Narayan
 
Glad that it worked.

Yes, Agree with Narayan sir. It makes the code much more crisp if we add it up in the collection loop. Thanks.
 
Thanks Naranyan.

They are indeed form control checkboxes, but the program seems to have a problem with the word "checkboxes" in the "For i = 1 To .CheckBoxes.Count" line.

Also, is it not necessary to include the final i number somewhere (i.e. from 1 to 48)?
 
Hi ,

Are these on a worksheet or on a form ?

I tried with 2 Form control checkboxes on a worksheet , and I did not face a problem.

If you have 48 checkboxes in your worksheet , that is what will be returned by the construct .Checkboxes.Count

Narayan
 
Hi ,

In that case try replacing the keyword Activesheet with another keyword Me , as in :

Code: 
Public Sub DisplayCheckboxesCaptions()
'          The following 4 lines of code will work with Form control checkboxes
            With Me
                For i = 1 To .CheckBoxes.Count
                    If .CheckBoxes(i).Value = Checked Then MsgBox .CheckBoxes(i).Caption
                Next
           End With
      
'          The following 4 lines of code will work with ActiveX control checkboxes
            With Me
                For i = 1 To .OLEObjects.Count
                    With .OLEObjects(i)
                         If .Name Like "CheckBox" & "*" Then If .Object.Value = True Then MsgBox .Object.Caption
                    End With
                Next
           End With
End Sub
Narayan
 
Nope, still highlighting ".Checkboxes" in that line, and displaying the error message "Compile error: Method or data member not found".
 
Hi ,

How are you executing this line of code ?

Is it part of the Form event code , or is it a subroutine which is called separately ?

If you can post your code , debugging will be immediate.

Narayan
 
Hi ,

This works :
Code: 
Private Sub UserForm_Click()
'           The following 4 lines of code will work with Form control checkboxes
             With Me
                 For i = 1 To .Controls.Count
                     If .Controls(i - 1).Name Like "CheckBox" & "*" Then If .Controls(i - 1).Value Then MsgBox .Controls(i - 1).Caption
                 Next
            End With
         
End Sub
Narayan
 
This won't let me post the existing code for some reason.

Your latest version pops up an Excel dialogue box with just the wording "CheckBox1" in it as soon as I click the command button to run the code.
 
Hi ,

That is what the code has been written to do.

I was not trying to write code to suit your application ; I posted the code to show how a loop could be used to reduce the number of lines of code , while still being able to access all the checkboxes.

If you want me to change this code to suit your application , please upload your file.

Narayan
 
Wish I could share the response from the client for whom this application was created - suffice it to say there are a lot of exclamation marks and words like "brilliant" and "awesome" thrown around! Thanks guys - you made a difference here!!

Narayan, never mind about the code for the loop thanks. It was brought to my attention this morning that there were minor issues with the info being copied to the clipboard - my fault: not all of the checkbox numbers corresponded with the associated label numbers that needed to be copied, so a loop is not going to work unless I put in the effort first to swap the labels around (and change their caption text) so that CheckBox 1 is next to Label1, etc. Much easier just to change the existing coding lines to link the CheckBox number to the actual Label number next to it!
 
Last edited:
You must log in or register to reply here.
Back
Top