Consulting

Results 1 to 4 of 4

Thread: Please Help! Code For Multiple Checkbox Returning to One cell in Excel VBA User Form

  1. #1

    Please Help! Code For Multiple Checkbox Returning to One cell in Excel VBA User Form

    Good afternoon all,

    After reading many articles and watching multiple youtube tutorials, I am still stuck on how to code my checkbox options to all report to a single cell (TargetRow, 18) - (S4:S100000) in "Database" (Excel Spreadsheet).

    This is what the page of my user form looks like:

    Form - Page 1.jpg

    For "Bait", I have checkbox options for:
    Sardine ("Check_Bait_Squid"),
    Squid ("Check_Bait_Sard"),
    Prawn ("Check_Bait_Prawn"),
    Redbait ("Check_Bait_Redbait"),
    Worm ("Check_Bait_Worm"),
    Mussel ("Check_Bait_Mussel"),
    Mullet ("Check_Bait_Mullet")

    If a user selects multiple checkboxes, which is allowed, I would like all responses to report to Column S (S4:S100000), separated by a comma.

    I am using my "Next" command button to save the data and move to the next page of the user form, simultaneously.

    I do already have these options assigned to a GroupName, "Bait".

    Please help with simple code that I can understand and modify as needed. I am a newbie, please keep that in mind.
    Last edited by Michelle RSC; 07-05-2022 at 06:17 AM. Reason: Forgot to add the Target Row

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,235
    Location
    Have a look at the Excel function TextJoin
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,396
    Location
    Lot of missing information in the post.

    I assume that Check_Bait_Squid (et. al) is the name of the checkbox control which reurns true or false

    Might give you some ideas

    
    Sub test()
        Dim s As String
    
    
        If Check_Bait_Squid Then s = "Squid,"
        If Check_Bait_Sard Then s = s & "Sard,"
        If Check_Bait_Prawn Then s = s & "Prawn,"
        If Check_Bait_Redbait Then s = s & "Redbait,"
        If Check_Bait_Worm Then s = s & "Worm,"
        If Check_Bait_Mussel Then s = s & "Mussel,"
        If Check_Bait_Mullet Then s = s & "Mullet,"
    
    
        If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)
        
        Range("S1234").Value = s
    
    
    
    
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4

    Nailed it! THANK YOU! Some filling in the blanks on my side, works like a dream

    Quote Originally Posted by Paul_Hossler View Post
    Lot of missing information in the post.

    I assume that Check_Bait_Squid (et. al) is the name of the checkbox control which reurns true or false

    Might give you some ideas

    
    Sub test()
        Dim s As String
    
    
        If Check_Bait_Squid Then s = "Squid,"
        If Check_Bait_Sard Then s = s & "Sard,"
        If Check_Bait_Prawn Then s = s & "Prawn,"
        If Check_Bait_Redbait Then s = s & "Redbait,"
        If Check_Bait_Worm Then s = s & "Worm,"
        If Check_Bait_Mussel Then s = s & "Mussel,"
        If Check_Bait_Mullet Then s = s & "Mullet,"
    
    
        If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)
        
        Range("S1234").Value = s
    
    
    
    
    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •