+ Reply to Thread
Results 1 to 4 of 4

Frame control loops its checkboxes & builds string

  1. #1
    Registered User
    Join Date
    08-16-2015
    Location
    Portsmouth
    MS-Off Ver
    7
    Posts
    3

    Question Frame control loops its checkboxes & builds string

    Hi I'm new to VBA and I'm completely stuck with an issue that is driving me to insanity!! I'm hoping someone will be able to help me please. I have added a UserForm1 to aid data entry in excel by the user. Inside the UserForm1, I have a multipage with 8 pages. Each page has a frame with multiple checkboxes. Once the SubmitButton is clicked, if checkboxes ticked the checkbox caption will be transferred to a designated cell in sheet Data Submitted. Frame1 checkboxes caption go to Cells(nr, 23) and Frame2 checkboxes go to .Cells(nr, 24). My problem is that I'm getting the Frame1 checkboxes caption on Cells(nr, 23) but it repeats to Cells(nr, 24) to!!! I've tried everything but unfortunately without no success. Please help?

    Please find an extract of the code below:

    Private Sub CmdButtonSubmitForm_Click()

    Dim ssheet As Worksheet
    Set ssheet = ThisWorkbook.Sheets("Data Submitted")
    nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

    Dim ctl As MSForms.Control
    Dim lines As String, delimiter As String

    For Each ctl In Me.Frame1.Controls
    If TypeName(ctl) = "CheckBox" Then
    If (ctl.Value) Then
    lines = lines & delimiter & ctl.Caption
    delimiter = " ; "
    End If
    End If
    Next

    With Sheet3
    .Cells(nr, 23).Value = lines
    End With

    For Each ctl In Me.Frame2.Controls
    If TypeName(ctl) = "CheckBox" Then
    If (ctl.Value) Then
    lines = lines & delimiter & ctl.Caption
    delimiter = " ; "
    End If
    End If
    Next

    With Sheet3
    .Cells(nr, 24).Value = lines
    End With
    EndSub

  2. #2
    Forum Contributor kalbasiatka's Avatar
    Join Date
    02-27-2016
    Location
    Brest, Belarus
    MS-Off Ver
    2021
    Posts
    224

    Re: Frame control loops its checkboxes & builds string

    An example would not prevent.
    To do it for me and help me it is 2 different things!
    Sorry for my english, blame Google translator

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Frame control loops its checkboxes & builds string

    It looks like the values of Lines (and Delimiter) are persisting between Frame loops.

    Add this line in the appropriate sections
    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Registered User
    Join Date
    08-16-2015
    Location
    Portsmouth
    MS-Off Ver
    7
    Posts
    3

    Re: Frame control loops its checkboxes & builds string

    It works perfectly!! I could hug you right now!!!
    Thank you so much for all your help mikerickson and for keeping me from pulling my hairs lol!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to use the Frame Control
    By oskar44 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2015, 02:30 AM
  2. Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro
    By krackaberr in forum Excel Programming / VBA / Macros
    Replies: 34
    Last Post: 03-05-2013, 11:12 AM
  3. Frame from Control Toolbox?!
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-17-2006, 05:10 PM
  4. [SOLVED] VBA control vscroll and hscroll in a frame
    By gary in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-29-2006, 12:40 PM
  5. Use VBA to control scrollbars in a Frame
    By gary in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-26-2006, 05:20 PM
  6. Assessing whether any checkboxes are checked within frame
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-16-2005, 05:05 PM
  7. user form and frame control
    By Gixxer_J_97 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1