Printing a string in Excel VBA after selecting multiple checkboxes

Question

I am using a userform on excel VBA to enter data into a different sheet but I have not found a way to concatenate multiple values together and put a comma in between each input. An example using the attached picture would be if Dusty Dry and Static were all selected the value that would be entered into the cell would be "Dusty, Dry, Static". Is there a way to do something like:

Range("R4").Value = If Dusty.Value = true then "Dusty"

user4039065 · Accepted Answer · 2018-07-09 16:04:19Z

2

Try,

Range("R4").Value = iif(dusty, "Dusty ", "") & _
                    iif(dry, "Dry ", "") & _
                    iif(damp, "Damp", "") & _
                    iif(static, "Static", "") & _
                    iif(abrasive, "Abrasive ", "") & _
                    iif(cohesive, "Cohesive ", "") & _
                    iif(hygroscopic, "Hygroscopic ", "") & _
                    iif(friable, "Friable ", ""))
Range("R4").Value = application.trim(Range("R4").Value)
Range("R4").Value = replace(Range("R4").Value, " ", ", ")

answered Jul 9, 2018 at 16:04

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

MrSparkyP Over a year ago

Thank you!! I believe that is exactly what I needed I am going to use it and see how it goes.

Vityata · Accepted Answer · 2018-07-09 16:05:01Z

2

If Dusty Then Range("R4") = "Dusty"

Which is short for:

If Dusty.Value = True Then 
    Range("R4").Value = "Dusty"
End If

For multiple checkboxes, something like this per checkbox should be ok:

If Dusty Then Range("R4") = Range("R4") & "Dusty" & vbCrLf
If Friable Then Range("R4") = Range("R4") & "Friable" & vbCrLf

It concatenates the values of the Range("R4") with the new value.

edited Jul 9, 2018 at 16:05

answered Jul 9, 2018 at 15:49

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Comments

Drew Chapin · Accepted Answer · 2018-07-09 16:33:28Z

You could also achieve this by looping through all of the controls on the form. Some of the benefits to this approach would be that you can add more checkboxes without having to modify the code, it's significantly less characters of code than the other approaches, and there is no redundant code (e.g. repeated used of iif() with different parameters).

The main drawback to this approach would be if you wanted the values in a sorted order. They will be sorted in the order in which the CheckBoxes were created.

Another consideration is if you have other CheckBox controls on the form you don't want this to apply to. You could add an additional check to look for a prefix on the .Name property, or set something in .Tag property, or put the CheckBoxes inside of a frame and change Me.Controls to Frame1.Controls.

Dim Control As Control, Data As String
For Each Control In Me.Controls
    If TypeName(Control) = "CheckBox" Then
        If Control.Value Then ' This is on a different line to prevent errors, VB checks all conditions of an If even if first one is false. 
            Data = Iif(Data <> "",Data & ", ","") & Control.Caption
        End If
    End If
Next

Range("R4").Value = Data

Notice how I used an Iif() to update Data for your concern about separating the values with a comma.

Collectives™ on Stack Overflow

Printing a string in Excel VBA after selecting multiple checkboxes

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related