CODEWITHSUNDEEP
excelvbauserform
MrSparkyP
MrSparkyP

Reputation: 21

Printing a string in Excel VBA after selecting multiple checkboxes

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"

enter image description here

Upvotes: 2

Views: 511

Answers (3)

Drew Chapin
Drew Chapin

Reputation: 7989

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.

Upvotes: 0

Vityata
Vityata

Reputation: 43585

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.

Upvotes: 2

user4039065
user4039065

Reputation:

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, " ", ", ")

Upvotes: 2

Related Questions