Sorting data from userform checkboxes

[pingwin77]

I have the following code assigned to a button on a form. When the person clicks the button, the data that is assigned to the currently check boxes goes to the designated tab. The issue that I am running into is that if only check box 1 and 6 are selected, I get a big blank area between them. How do I set it up so that all of the data only has one space between it and the next bit of data no mater how many boxes are checked?

The other issue I am having is that a few of the check boxes return multiple cells worth of data. Box 2, Box 3 and Box 6 all return 2 rows worth of data. I need these the way they are for formatting purposes. Box 5 generates data in 4 cells. I need all of the information for each check box to stay together.

Example:

BOX 1 DATA
space
BOX 3 DATA 1
BOX 3 DATA 2
space
BOX 4 DATA
space
BOX 6 DATA 1
BOX 6 DATA 2

I also have attached a spreadsheet showing what I am working with. The data in cells B2:F10 is generated from the code below, (The cell reference don't match because I cut the data from another page for security reasons) Any cells in that range that are colored need to stay together in the same format. The bolcks of colored data below are examples of a few different ways i need the data to look depending on what check boxes are selected.

let me know if you have any questions. THANKS!

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Sheets("Comments").Select
    Range("Q1:X18").Select
    Selection.ClearContents
    If CheckBox1.Value = True Then ActiveWorkbook.Sheets("Comments").Range("S2").Value = " The stated uncertainty of the measured values has not been taken into account for the pass / fail indicators."
    If CheckBox1.Value = True Then ActiveWorkbook.Sheets("Comments").Range("R2").Value = "TRUE"
    If CheckBox2.Value = True Then ActiveWorkbook.Sheets("Comments").Range("S3").Value = "*An asterisk in the scope column indicates that those test results are not covered by our current A2LA"
    If CheckBox2.Value = True Then ActiveWorkbook.Sheets("Comments").Range("S4").Value = "accreditation."
    If CheckBox2.Value = True Then ActiveWorkbook.Sheets("Comments").Range("R3:R4").Value = "TRUE"
    If CheckBox3.Value = True Then ActiveWorkbook.Sheets("Comments").Range("S5").Value = "This Certificate of Inspection includes additional pages of inspection results supplied electronically to the"
    If CheckBox3.Value = True Then ActiveWorkbook.Sheets("Comments").Range("S6").Value = "customer."
    If CheckBox3.Value = True Then ActiveWorkbook.Sheets("Comments").Range("R5:R6").Value = "TRUE"
    If CheckBox4.Value = True Then ActiveWorkbook.Sheets("Comments").Range("S7").Value = "This Certificate of Inspection was completed using a customer report template."
    If CheckBox4.Value = True Then ActiveWorkbook.Sheets("Comments").Range("R7").Value = "TRUE"
    If CheckBox5.Value = True Then ActiveWorkbook.Sheets("Comments").Range("S8").Value = "Temp:"
    If CheckBox5.Value = True Then ActiveWorkbook.Sheets("Comments").Range("U8").Value = "Humidity:"
    If CheckBox5.Value = True Then ActiveWorkbook.Sheets("Comments").Range("R8").Value = "TRUE"
    If CheckBox6.Value = True Then ActiveWorkbook.Sheets("Comments").Range("S9").Value = "Additional Notes:"
    If CheckBox6.Value = True Then ActiveWorkbook.Sheets("Comments").Range("R9:R10").Value = "TRUE"
    If CheckBox5.Value = True Then ActiveWorkbook.Sheets("Comments").Range("T8").Value = TextBoxTEMP.Value
    If CheckBox5.Value = True Then ActiveWorkbook.Sheets("Comments").Range("V8").Value = TextBoxHUMID.Value
    If CheckBox6.Value = True Then ActiveWorkbook.Sheets("Comments").Range("S10").Value = TextBox1.Value
    ActiveSheet.Range("A1").Select
    Unload Me
End Sub

[Bob Phillips]

[vba]

Private Sub CommandButton1_Click()
Dim rOff As Long, sOff As Long, tOff As Long, uOff As Long, vOff As Long
Application.ScreenUpdating = False

With ActiveWorkbook.Sheets("Comments")

rOff = 2: sOff = 2: tOff = 2: uOff = 2: vOff = 2 'Adjust as required
Sheets("Comments").Range("Q1:X18").ClearContents
If CheckBox1.Value Then
.Cells(sOff, "S").Value = " The stated uncertainty of the measured values has not been taken into account for the pass / fail indicators."
.Cells(rOff, "R").Value = "TRUE"
sOff = sOff + 1
rOff = rOff + 1
End If
If CheckBox2.Value Then
.Cells(sOff, "S").Value = "*An asterisk in the scope column indicates that those test results are not covered by our current A2LA"
.Cells(sOff + 1, "S").Value = "accreditation."
.Cells(rOff, "R").Value = "TRUE"
.Cells(rOff + 1, "R").Value = "TRUE"
sOff = sOff + 2
rOff = rOff + 2
End If
If CheckBox3.Value Then
.Cells(sOff, "S").Value = "This Certificate of Inspection includes additional pages of inspection results supplied electronically to the"
.Cells(sOff + 1, "S").Value = "customer."
.Cells(rOff, "R").Value = "TRUE"
.Cells(rOff + 1, "R").Value = "TRUE"
sOff = sOff + 2
rOff = rOff + 2
End If
If CheckBox4.Value Then
.Cells(sOff, "S").Value = "This Certificate of Inspection was completed using a customer report template."
.Cells(rOff, "R").Value = "TRUE"
sOff = sOff + 1
rOff = rOff + 1
If CheckBox5.Value = True Then
.Cells(sOff, "S").Value = "Temp:"
.Cells(tOff, "T").Value = TextBoxTEMP.Value
.Cells(vOff, "V").Value = TextBoxHUMID.Value
.Cells(uOff, "U").Value = "Humidity:"
.Cells(rOff, "R").Value = "TRUE"
rOff = rOff + 1
sOff = sOff + 1
Otff = tOff + 1
uOff = uOff + 1
vOff = vOff + 1
End If
If CheckBox6.Value = True Then .Range("S9").Value = "Additional Notes:"
.Cells(sOff, "S").Value = TextBox1.Value
.Cells(rOff, "R").Value = "TRUE"
.Cells(rOff + 1, "R").Value = "TRUE"
sOff = sOff + 1
rOff = rOff + 2
End If
End With
ActiveSheet.Range("A1").Select
Unload Me
End Sub
[/vba]

[pingwin77]

rOff = 2: sOff = 2: tOff = 2: uOff = 2: vOff = 2 'Adjust as required

what are the adjustments for?

and should there be a "End if" between checkbox 4 and 5? If so, should the lines for checkbox 5 be moved out more so they line up with the rest of the code?

[RonMcK]

<deleted by RonMcK>

[Bob Phillips]

rOff = 2: sOff = 2: tOff = 2: uOff = 2: vOff = 2 'Adjust as required

what are the adjustments for?

That is the start row for each of the columns.

[pingwin77]

I plugged that code in and here is a workbook showing the resulting data. I went through and added one additional offset to each bit of data so it would come into my sheet with the spaces in it. The first 5 checkbox values seem to be just fine. The temp and humidity values I can adjust to make them go where needed. The "additional comments" data seems off. in the attached workbook it should come into row 14 not 9. if we can do that and then move the data from textbox 1 to populate directly below "Additional Comments:" in column S that would be fantastic!

[Bob Phillips]

There is no code or checkboxes in that workbook.

[pingwin77]

I know, that is the results from the code you provided above. I am just looking for a couple changes that are noted in my previous post.

[Bob Phillips]

I don't know what you changed in the code, and so on, so I am working in a vacuum.

[pingwin77]

I added the button and the code to go along with this. Click the button on the sheet and then click the "continue" button. Check all the boxes and type numbers in for the temp, humidity and notes in the additional notes box. The info to the right of the button is what is currently produced. I would like it to look like the info below that.

[pingwin77]

Anyone able to help out with this? I am trying to get this project wrapped up before the end of this week.

[lucas]

What is this file not doing that you want it to do?

be as specific as possible.

[pingwin77]

I attached a new version of the sheet I am working with. There are 3 sets of data to the right of the button.

First - The actual data that comes from running the code. If you would like to run it here is what you do:
1. Click the button
2. Click the "Continue Button"
3. Check all the boxes on the form and type in values for the text boxes
4. Click the "finish report" button.

Second - A copy of the data in the first group with the changes needed highlighted two diferent colors. The two color groups are independent of each other but as a group they need to stay in the same orientation.

Third - shows what the desired outcome is. The sames cells are still highlights in the 2 colors (***the BLACK should be ORANGE that is used with the additional notes***) but now they are moved to where they need to be. The boxes indicate the data that is generated from each check box. These need to remain in the same format. (ex. if there are two rows of info in one box, it needs to stay that way)

This code is supposed to generate a list of the values that are checked on the second form. The main issue is that not all of the boxes will be checked with each job, only the ones that are needed. This is supposed to make a list of all the checked boxed so that there is only 1 row of space between what is selected.

The second tab in the workbook shows some desired possible outcomes from the form.

Let me know if you have any questions. It is hard to explain all this stuff through typing.

[pingwin77]

Just checking to see if any questions have arised?