Comma Between the CheckBox Values into one cell

  • 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





  • 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?

  • 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

  • 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.

  • 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

  • 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

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!