pingwin77
12-08-2008, 09:49 AM
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
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