Comma Between the CheckBox Values into one cell

asmita.nda · Jul 6th 2016

Hi,
I've been trying to write out a code for ticking off multiple check boxes so that the resultant values comes in one cell separated by a comma. Below is the code I've written it out.
It'd be really helpful if someone can help me figure out what's wrong with this code.

Thanks

Code

Private Sub cmdSave_Click()
    Dim lngWriteRow As Long
        




    Dim ws As Worksheet
    Set ws = Worksheets("details")
    
    lngWriteRow = ws.Cells(Rows.Count, 7) _
    .End(xlUp).Offset(1, 0).Row




        ws.Range("A" & lngWriteRow) = txtFirstName.Value
        ws.Range("B" & lngWriteRow) = txtLastName.Value
        ws.Range("C" & lngWriteRow) = txtPosition.Value
        ws.Range("D" & lngWriteRow) = txtCompany.Value
        ws.Range("E" & lngWriteRow) = txtEmail.Value
        ws.Range("F" & lngWriteRow) = txtPhoneW.Value
        ws.Range("G" & lngWriteRow) = txtPhoneM.Value
        ws.Range("H" & lngWriteRow) = txtAddress.Value
        
        If CheckBox1.Value = True Then
            ws.Range("J" & lngWriteRow) = "Construction"
        End If
            
        If CheckBox2.Value = True Then
            ws.Range("J" & lngWriteRow) = ws.Range("J" & lngWriteRow) & IIf(ws.Range("J", lngWriteRow) <> "", ",", "") & "Infrastructure"
        End If
        
        If CheckBox3.Value = True Then
            ws.Range("J" & lngWriteRow) = ws.Range("J" & lngWriteRow) & IIf(ws.Range("J", lngWriteRow) <> "", ",", "") & "Consultant"
        End If
        
        If CheckBox4.Value = True Then
            ws.Range("J" & lngWriteRow) = ws.Range("J" & lngWriteRow) & IIf(ws.Range("J", lngWriteRow) <> "", ",", "") & "Resources/Mining"
        End If
        
        If CheckBox5.Value = True Then
            ws.Range("J" & lngWriteRow) = ws.Range("J" & lngWriteRow) & IIf(ws.Range("J", lngWriteRow) <> "", ",", "") & "Supplier"
        End If
        
        If CheckBox6.Value = True Then
            ws.Range("J" & lngWriteRow) = ws.Range("J" & lngWriteRow) & IIf(ws.Range("J", lngWriteRow) <> "", ",", "") & "Education"
        End If
        
        If CheckBox7.Value = True Then
            ws.Range("J" & lngWriteRow) = ws.Range("J" & lngWriteRow) & IIf(ws.Range("J", lngWriteRow) <> "", ",", "") & "Competitor"
        End If
        
        If CheckBox8.Value = True Then
            ws.Range("J" & lngWriteRow) = ws.Range("J" & lngWriteRow) & IIf(ws.Range("J", lngWriteRow) <> "", ",", "") & "Friend"
        End If
        
        If CheckBox9.Value = True Then
            ws.Range("J" & lngWriteRow) = ws.Range("J" & lngWriteRow) & IIf(ws.Range("J", lngWriteRow) <> "", ",", "") & "Government"
        End If
        
        If CheckBox10.Value = True Then
            ws.Range("J" & lngWriteRow) = ws.Range("J" & lngWriteRow) & IIf(ws.Range("J", lngWriteRow) <> "", ",", "") & "Commercial Retail"
        End If
        
        If CheckBox11.Value = True Then
            ws.Range("J" & lngWriteRow) = ws.Range("J" & lngWriteRow) & IIf(ws.Range("J", lngWriteRow) <> "", ",", "") & "Private Company"
        End If
        
        If CheckBox12.Value = True Then
            ws.Range("J" & lngWriteRow) = ws.Range("J" & lngWriteRow) & IIf(ws.Range("J", lngWriteRow) <> "", ",", "") & "Health Planning"
        End If


End Sub

Display More

**StephenR** · Jul 6th 2016

Re: Comma Between the CheckBox Values into one cell

It's a bit hard to tell what's wrong if we don't know what happens when you run it, what your data looks like, and the results you expect. Go on, help us out a little.

But this line for example

Code

ws.Range("J" & lngWriteRow) = ws.Range("J" & lngWriteRow) & IIf(ws.Range("J", lngWriteRow) <> "", ",", "") & "Infrastructure"

appends "Infrastructure in all cases, the only difference being comma or blank before it. Not sure that is intended?

asmita.nda · Jul 7th 2016

Re: Comma Between the CheckBox Values into one cell

Hi Stephen,

The error I'm getting is this:
[ATTACH=CONFIG]69607[/ATTACH]

I'm still learning VBA right now and one of the issues I'm facing is not being able to have multiple checkboxes values in one cell separated by a comma.

The whole point of this code is that I'm constructing a contact database (as you can see a bit in the attached image above) where I want to be able to add, edit, delete and search.

But currently, I'm struggling with the fact that if I click on more than one check-boxes; I'm not able to configure a code on how to separate them with a comma.

I hope this helps; if not I'd attach my excel file next time.

Thanks in advance

**StephenR** · Jul 7th 2016

Re: Comma Between the CheckBox Values into one cell

I get eyestrain looking at pictures like that. Please post a workbook.

Infomage · Jul 7th 2016

Re: Comma Between the CheckBox Values into one cell

I think you might have a problem with the use of Iif. See http://www.fmsinc.com/free/NewTips/NET/NETtip33.asp

**norie** · Jul 7th 2016

Re: Comma Between the CheckBox Values into one cell

In your Iif statements you have ws.Range("J", lngWriteRow) it should be ws.Range("J" & lngWriteRow).

By the way, here's another way you could handle the checkboxes.

Code

Dim cnt As Long
Dim I As Long
Dim arrOutput()
Dim arrSectors As Variant


    ' other code


    arrSectors = Array("Construction", "Infrastructure", "Consultant", "Resources/Mining", "Supplier", "Education", _
                       "Competitor", "Friend", "Government", "Commercial Retail", "Private Company", "Health Planning")


    For I = LBound(arrSectors) To UBound(arrSectors)
        If Me.Controls("CheckBox" & I + 1).Value = True Then
            ReDim Preserve arrOutput(cnt)
            arrOutput(cnt) = arrSectors(I)
            I = I + 1
        End If
    Next I


    ws.Range("J" & lngWriteRow).Value = Join(arrOuput, ",")

Display More

asmita.nda · Jul 8th 2016

Comma Between the CheckBox Values into one cell and delete from list - Part 2

forum.ozgrid.com/index.php?attachment/69612/

Hi Steven,

Attached is the file.
I've managed to get the comma working. Just another issue with deleting too. As in the userform when I can search, I want to be able to select a contact in the list and delete the entire selected row alongwith deleting the contact row from the worksheet too, shifting the other rows and cells up.

Hope you can answer.

Thanks in advance

asmita.nda · Jul 8th 2016

Re: Comma Between the CheckBox Values into one cell

Hi Steven,http://"http://www.ozgrid.com/forum/showthread.php?t=200381&p=773551#post773551"

http://www.ozgrid.com/forum/showthread.php?t=200381&p=773551#post773551

I've made a new post to this as I couldnt upload the template here.
Thanks for your help in advance

asmita.nda · Jul 8th 2016

Re: Comma Between the CheckBox Values into one cell

Thankyou Norie for your help. That worked for me. I just made the change from ws.Range("J", lngWriteRow) to ws.Range("J" & lngWriteRow)

Just that how should I get space between a comma and the word itself like for example, currently my cell shows Construction,Private instead of Construction, Private.

Also at this link I've attached my template: http://www.ozgrid.com/forum/sh…00381&p=773551#post773551

Another problem I'm facing is that I've been unable to update and delete the contact off the contactlist on the worksheet. It would be great help if I could get this working.

Thankyou so much in advance

**norie** · Jul 8th 2016

Re: Comma Between the CheckBox Values into one cell

If you want to put a space after the comma just change "," to ", " in the Join function code I posted.

Code

ws.Range("J" & lngWriteRow).Value = Join(arrOuput, ", ")

**norie** · Jul 8th 2016

Re: Comma Between the CheckBox Values into one cell

asmita.nda

I've moved the post from the new thread you started to this thread.

asmita.nda · Jul 8th 2016

Re: Comma Between the CheckBox Values into one cell

Thanks heaps Norie

ALso,

http://www.ozgrid.com/forum/sh…00383&p=773558#post773558

Can someone please have a look at this template and help me with deleting the contacts.

Thanks

**norie** · Jul 8th 2016

Re: Comma Between the CheckBox Values into one cell

Don't you already have code to do that behind the Delete button?

asmita.nda · Jul 8th 2016

Re: Comma Between the CheckBox Values into one cell

Yeah, but I don't know how to delete the specific contacts that you select on the list (in userform). Because as of now, the delete button deletes any row rather than the one you select.

I don't know what changes need to be made to that delete button code. Does that make sense?

Comma Between the CheckBox Values into one cell

Participate now!

Share