Userform help - Multiple selection box copy to clipboard

[kinetekerz]

Hi!
Pretty novice as only just started at this so apologies!

I'm looking to create a userform within excel to assist repetitive typing.

It needs to compose of multiple check boxes that would copy the context of that check box, and have the option to select multiple amounts of these check boxes to then copy to clipboard. If multiple boxes are selected they would need to be spaced or paragraph formatted in between these if possible.
There also needs to be 2 option boxes above these which would read, yes or no, depending which is selected this context would be copied and placed into the clipboard aswell.

So for example the option boxes would ask: Yes or No (this is a mandatory input) this would then copy the relevant Yes or No into the clipboard.

Then the check boxes would state for example: Requested X, Requested Y, Requested Z.
Selecting 1 or multiple of these would again need to copy the context of that box to the clipboard and as mentioned before a paragraph space if multiple boxes are selected.

So what I would want the pasted text to look like for example would be (I wish for the same formatting)

Yes
Requested X
Requested Y
Requested Z

Where would I start with the macros / code to make this work?

Any help is much appreciated!

 

[tlowry]

Hello kinetekerz and welcome to MrExcel!


Here is my suggested solution to your request. I have made some modifications to the design to make the programming more “user proof” and easier to program.

1. I used a multiple selection Listbox with options set to make it look like a series of CheckBoxes.
2. I set one of the OptionButtons to true at the start. This puts the form in an acceptable condition at the start.

Install instructions:

• Go to design mode (Alt F11)
• Create a UserForm

Insert UserForm
​

• Add OptionButton Controls to form

OptionButton1
​

OptionButton2
​

• Add Listbox Control

ListBox1
​

• Add CommandButton Control to form

CommandButton1
​

• Copy the code into the form’s module

Right click on form
​

Select View code
​

Copy & paste code
​

• Exit design mode (Alt F11)
• Save the project
• Test and test
• Post testing results

Option Explicit

Private Sub CommandButton1_Click()
    Dim msgOut, i
    Dim objData As New MSForms.DataObject
    msgOut = IIf(Me.OptionButton1 = True, _
            Me.OptionButton1.Caption, Me.OptionButton2.Caption) & vbCrLf
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) = True Then _
            msgOut = msgOut & Me.ListBox1.List(i) & vbCrLf
    Next i
    msgOut = Left(msgOut, Len(msgOut) - 1)
    MsgBox msgOut
    objData.SetText msgOut
    objData.PutInClipboard
End Sub
Private Sub UserForm_Initialize()
    With Me.ListBox1
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
        .BackColor = Me.BackColor
        .BorderStyle = fmBorderStyleNone
        .SpecialEffect = fmSpecialEffectFlat
        .AddItem "Requested X"
        .AddItem "Requested Y"
        .AddItem "Requested Z"
    End With
        Me.OptionButton1.Value = True
End Sub

 

[kinetekerz]

Hi, tlowry !

That does exactly what i need it to do, huge thanks! I had a further query i hope you could assist with if possible?

I am also looking to add an optional text box with check box next to it, so in case there's something that isn't in the list, you can select the check box and type into the text next to it, this will also be copied to the clip board. Do you know how to add this?

Also, Is it possible to launch directly into a user form without having to click a command button? If this isn't possible, when excel loads is it possible to set it to only show the command button, showing none of the toolbar or chart surrounding it?

Thanks again!

 

[kinetekerz]

The launching directly into the user form i have sorted, just that optional text box now!