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:
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().
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:
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.
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()
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.