Help Checking Field Values Related To UserForm CheckBox Captions And Re-Check CheckBoxes In UserForm

ryancdavis

New Member
Joined
Jun 29, 2017
Messages
8
Hi -

Hopefully someone could help me out. I've been trying for hours to figure this out on my own, but can't seem to get it. Any help is sincerely appreciated.

I am working on a proposal format that has project 'Inclusions' and 'Exclusions. I have a button next to the inclusion area on a sheet named 'Proposal' that when clicked activates UserForm1. UserForm1 has 30 checkboxes that I can select based on what I want to include in my proposal. All of the checkboxes that I check get filled into a merged cell on my sheet proposal within the inclusions section (A114 to K126), and anything that is not checked automatically get's filled to the exclusions cell right below it (A129 to K144). This part of the code is working great. The issue that I am having is that if I want to come back and revise what I am including, when I re-open the userform none of the check boxes are checked and I have to start all over.

The code that I use to write the userform checkbox captions to the specific cells on the sheet is below:
Code:
Private Sub CommandButton2_Click()
Dim chk As Control
Dim included As String, i_delimiter As String
Dim excluded As String, e_delimiter As String

For Each chk In Me.frameGeneral.Controls
    If TypeOf chk Is MSForms.CheckBox Then
        If (chk.Value) Then
            included = included & i_delimiter & chk.Caption
            i_delimiter = " | "
            Else
            excluded = excluded & e_delimiter & chk.Caption
            e_delimiter = " | "
        End If
    End If
Next
With Sheets("Proposal")
    .Range("A114:K126").Value = included
    .Range("A129:K144").Value = excluded
    Unload UserForm1
End With
End Sub

So, what I am wanting to do is reverse engineer the code above to check the inclusions cell range (specifically A114 to K126) to see if the checkbox caption exists, and if so, then visibly check the checkbox automatically in the userform upon UserForm_Initialize().
Code:
UserForm_Initialize()
This way when the userform is reloaded, everything that I included would be checked since the code is checking the cell to see if the value exists. This would also be helpful if the workbook was saved and closed, but re-opened later, since the userform will always check what values exist and check the checkboxes accordingly.

I initially was using this code below to check if the checkbox caption value existed and if so it would automatically check the checkbox in the userform, but writing this code for 30 (and possibly more checkboxes in the future) is a daunting and time consuming process:
Code:
Private Sub UserForm_Initialize()
If Range("A114").Value = CheckBox1.Caption Then
        CheckBox1.Value = 1
    Else
        CheckBox1.Value = 0
        End If
End Sub

So, I am trying to write something with a loop (I think?) that would check if the value exists for all the checkboxes and then check the checkbox automatically so I don't have to write a code individually for each checkbox.

Can someone help me out and let me know how to accomplish this? I really appreciate the help and look forward to hearing back from someone. Thank you.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Try passing the exclusion or inclusion string to this sub.
Note that the Delimiter defaults to "|" with no spaces, but can be specified to meet your need.
Note also that the ValueToSet can be altered to suit.
Finally, one need only pass either the Inclusion string (ValueToSet:=True) or the Exclusion string (ValueToSet:=False), but not both.

Code:
Sub SetCheckBoxesNamed(byVal strNames as String, optional Delimiter as String = "|", optional ValueToSet as Boolean = True)
    Dim OneControl as MSForms.Control
    
    strNames = LCase(Delimiter & strNames & Delimiter)

    For each oneControl in Me.frameGeneral.Controls
        If TypeName(oneControl) = "CheckBox" Then
            oneControl.Value = (InStr(strNames, LCase(Delimiter & oneControl.Caption & Delimiter)) = 0) Xor ValueToSet
        End If
    Next oneControl
End Sub
 
Upvote 0
Hi Mike -

Thank you so much for the quick reply. I sincerely appreciate the help and quick response.

I am really new to VBA, but excited to learn how it works. I wanted to make sure I knew how to 'pass the exclusion or inclusion string to this sub." To accomplish this, do I copy-and-paste the code you included into the UserForm_Initialize() sub? Since the code you wrote is within its own sub, how do I pass the string properly?
 
Upvote 0
The code that I wrote is to be put in its own procedure, not inside a different sub.

Your Initialize event might look like

Code:
Private Sub UserForm_Initialize()
    Dim CheckThese as String

    CheckThese = Range("A114").Value 

    Call SetCheckBoxesName(CheckThese)
End Sub
 
Upvote 0
Thank you for the clarification. I would not have figured that out, so I really appreciate it. Also, thank you for providing the code showing how to call the SetCheckBoxesName sub. I will give this a go tomorrow and report back. Thank you again and talk to you soon.
 
Upvote 0
Hi Mike -

I was so excited to try it (even though it is 2:00AM in the morning here in Oklahoma and I've got to be at the office at 7:00AM in the morning), but wanted to let you know that IT WORKED! You are awesome! Thank you so much. It works beautifully.

I wanted to ask you if I added an additional frame (like for example Me.frameInstallation.Controls) and wanted to implement the same code for another frame, how would I do that? Would I just copy and paste the same code and just change the frame name or is there a better way to apply the same code to multiple frames?

Also, if I ended up moving the frames to named tabs in the userform (like tab 'General' and tab 'Installation'), does this effect the code in any way?

Thank you again for the help. I can't express how much you have helped me after all the hours I've spent with trial and error trying to get it to work.
 
Upvote 0
Hi Mike -

Hope you are doing well! It has been a while!

I've resurrected this workbook recently and have been working to make some modifications to the VBA code you helped me with previously. I'm still having some trouble revising the code after investing many hours over the past few weeks trying to figure it out myself - so I am needing a little help :).

Originally, I had multiple tabs within the UserForm and each tab had a single dedicated Frame that was referenced in the VBA code. I created multiple check boxes manually and named them within the various frames. The original code would check all Frames within the various tabs where the check boxes reside, and if checked (selected), it would add the checkbox name to the predefined cell range (inclusions section) of the worksheet and if they were not checked it would add these to the other predefined cell range (exclusions section) of the sheet.

What I am trying to do now is to have the UserForm checkbox selection populate based on a predefined range of values that I would list in the workbook (probably on a different sheet) for the various "scopes" (since each scope will be a little different). I'll probably have 3 different lists of "options" for the various scopes. I really would like to only show (or populate) the UserForm with the list of predefined options as check boxes, which would be selected based on a drop down list in another cell. The value of this single cell would determine which list of predefined options are used and build the UserForm check boxes only for these specific items.

As an example, if the value "Structural" is selected in cell A1 (which is populated using a predefined drop down list specified on another sheet), then the UserForm would only show a tab/frame with these values and they would be shown as check boxes. Then, as the code did before, I'd like to have it check which ones are selected and add them to a specific range of cells on the worksheet, and if not selected, have them added to a different cell range on the worksheet (like before).

Is this possible, or is there a better way to accomplish this effort?

I've included the most recent version of the code you helped me with below for review. Thank you for your help in advance - I sincerely appreciate it.

Code:
Sub SetCheckBoxesNamed(ByVal strNames As String, Optional Delimiter As String = ", ", Optional ValueToSet As Boolean = True)
    Dim OneControl As MSForms.Control
    
    strNames = LCase(Delimiter & strNames & Delimiter)

    For Each OneControl In Me.frameStructural.Controls
        If TypeName(OneControl) = "CheckBox" Then
            OneControl.Value = (InStr(strNames, LCase(Delimiter & OneControl.Caption & Delimiter)) = 0) Xor ValueToSet
        End If
    Next OneControl
    For Each OneControl In Me.frameStructuralHC.Controls
        If TypeName(OneControl) = "CheckBox" Then
            OneControl.Value = (InStr(strNames, LCase(Delimiter & OneControl.Caption & Delimiter)) = 0) Xor ValueToSet
        End If
    Next OneControl
     For Each OneControl In Me.frameCivil.Controls
        If TypeName(OneControl) = "CheckBox" Then
            OneControl.Value = (InStr(strNames, LCase(Delimiter & OneControl.Caption & Delimiter)) = 0) Xor ValueToSet
        End If
    Next OneControl
End Sub

Code:
Private Sub SaveIEClose_Click()
Dim chk As Control
Dim included As String, i_delimiter As String
Dim excluded As String, e_delimiter As String
For Each chk In Me.frameStructural.Controls
    If TypeOf chk Is MSForms.CheckBox Then
        If (chk.Value) Then
            included = included & i_delimiter & chk.Caption
            i_delimiter = ", "
            Else
            excluded = excluded & e_delimiter & chk.Caption
            e_delimiter = ", "
        End If
    End If
Next
For Each chk In Me.frameStructuralHC.Controls
    If TypeOf chk Is MSForms.CheckBox Then
        If (chk.Value) Then
            included = included & i_delimiter & chk.Caption
            i_delimiter = ", "
            Else
            excluded = excluded & e_delimiter & chk.Caption
            e_delimiter = ", "
        End If
    End If
Next
For Each chk In Me.frameCivil.Controls
    If TypeOf chk Is MSForms.CheckBox Then
        If (chk.Value) Then
            included = included & i_delimiter & chk.Caption
            i_delimiter = ", "
            Else
            excluded = excluded & e_delimiter & chk.Caption
            e_delimiter = ", "
        End If
    End If
Next
With Sheets("Proposal")
    .Range("A136:K153").Value = included
    .Range("A156:K173").Value = excluded
End With
Call IEN_TextBox
Unload UserForm1
End Sub

Code:
Private Sub UserForm_Initialize()
    Dim CheckThese As String
    CheckThese = Worksheets("Proposal").Range("A136").Value 'MAY NEED TO CHANGE THIS RANGE IF I END UP MOVING THE INCLUSIONS TO ANOTHER CELL
    Call SetCheckBoxesNamed(CheckThese)
    If Range("A189").Value = "THIS SECTION HAS INTENTIONALLY BEEN LEFT BLANK." Then
    Me.IENTextBox.Text = ""
    Else
    Me.IENTextBox.Text = CStr(ThisWorkbook.Sheets("Proposal").Range("A189").Value)
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top