CODEWITHSUNDEEP
excelvba
Antony Whittle
Antony Whittle

Reputation: 11

Concatenate text in VBA Excel with certain paramaters

What method would be best if I had the following.

3 checkboxes that if selected will populate 3 cells A1, A2 & A3. I need code that will check the 3 cells for data and create a singular string of info. I can use this with formula but need VBA to do the following to the cells as per below.

So my end result will be "cell1" (if only 1 checkbox is selected) or "cell1 & cell2" (if 2 checkboxes are selected) but if all 3 are selected I want the string to read "cell1, cell2 & cell3". There will only ever be 3 cells in this section.

I want the Concatenate Text to then go into a single cell. AA13 where it can be copied at a later stage.

I have another scenario where I have 6 checkboxes and I need the same thing as above, the 6 checkboxes populate 6 cells B1:B6. but, I need some code that makes sure a minimum of 2 check box's are selected. I can't work out how to get VBA to check for the number of tick boxes and report an error if it doesn't meet the criteria of 2 minimum.

I will have an active x button to run the code as per above to concatenate but when a check box is selected the cell will auto populate with the string i need.

Help would be much appreciated. I'm struggling to find code online to help me for these two scenarios

Upvotes: 1

Views: 115

Answers (1)

Gary's Student
Gary's Student

Reputation: 96773

Consider:

Sub konkat()
    Dim N As Long
    With Application.WorksheetFunction
        N = .CountA(Range("A1:A3"))
        Select Case N
            Case 0
                Exit Sub
            Case 1
                Range("AA13").Value = Range("A1") & Range("A2") & Range("A3")
            Case 2
                Range("AA13").Value = .TextJoin(" & ", True, Range("A1:A3"))
            Case 3
                Range("AA13").Value = Range("A1").Value & " , " & Range("A2").Value & " & " & Range("A3").Value
        End Select
    End With
End Sub

Upvotes: 0

Related Questions