Checkbox Can I do This

[Dave69rock]

I have a userform that contains 15 labels, each label has Numbers in it each time i click on a label the text is inserted into an excel spreadsheet. I use it as a fast input solution and works well and has done for a few years.

I Now want to Add three checkboxes to the userform
If checkbox One is ticked i want the letter "A" and the number in the label i choose to be inserted into the spreadsheet.
Checkbox Two "B"
Checkbox Three "c"

I would only ever tick one of the checkboxes never two or three so it would be A, B or C

This is the code for The Labels i use

private Sub Label4_Click()
Call InsertTextSelection(Label4.Caption, RGB(0, 0, 255), 0)
End Sub

Private Sub Label5_Click()
Call InsertTextSelection(Label5.Caption, RGB(0, 0, 0), 0)
End Sub

and so on

This is the Call Insert textselection

Public Sub InsertTextSelection(text, colour, hours)
    UserForm1.Hide
    r = Selection.Row
    c = Selection.Column
    Range(Cells(r, c - 0), Cells(r, c - 0)).Select
    With Selection
        .ClearContents
        .Font.Size = 9
        .Font.Color = colour
        .MergeCells = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Value = text
    End With
    
    End Sub

The userform works by doubleclick

Hope you Can help

Dave

[Leith Ross]

Hello Dave69rock,

Please remember to wrap your code using the Code tags.

How to wrap your Code
On the Message window Toolbar you will see the # icon. This will automatically wrap the text you selected with the proper VB tags to create a Code Window in your post. You can do this manually by placing the tag [code] at the start of the line, and the tag [/code] at the end.

Sincerely,
Leith Ross

[Dave69rock]

Didn't know still new to the site

its done and will be in future

[Leith Ross]

Hello Dave69rock,

The easiest solution would be to declare a Public string variable to hold the letter. The Click event of each check box would set this variable to the proper letter (checked) or clear it (unchecked).

Sincerely,
Leith Ross

[Dave69rock]

Thanks leith my knowledge of vb isn't to that standard any chance of an example.

Dave

[mikerickson]

You could change this line in InsertTextSelection

    .Value = Switch(CheckBox1.Value, "A", CheckBox2.Value "B", CheckBox3.Value, "C", True, vbNullstring ) & text

[Leith Ross]

Hello Dave69rock,

I'll need a little time to put together a sample workbook for you as a guide.

Sincerely,
Leith Ross

[Dave69rock]

Leith

Wow this is great many thanks
Dave

[Dave69rock]

Mikerickson thanks for your reply

i can't seem to get it to work i replaced the Call InsertTextSelection with what you said.

Dave

[mikerickson]

I expressed myself poorly. Leave the calls for the sub as they are and change the sub to

Public Sub InsertTextSelection(text, colour, hours)
    UserForm1.Hide
    r = Selection.Row
    c = Selection.Column
    Range(Cells(r, c - 0), Cells(r, c - 0)).Select
    With Selection
        .ClearContents
        .Font.Size = 9
        .Font.Color = colour
        .MergeCells = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Value = Switch(CheckBox1.Value, "A", CheckBox2.Value, "B", _
                      CheckBox3.Value, "C", True, vbNullstring ) & text
    End With
    
    End Su

b

[Dave69rock]

Mikerickson

Thank you so much i love how your code works.

Dave