My worksheet contains 27 columns (A to AA) and has multiple content, 'normal data' like names/certificates, dates and checkboxes. With this sheet i can keep myself informed about when a certificated has to be renewed. First i will give some background information.
I do this like this:
*1
*2
Checkbox 3 and 4 are the same as above, but reffer to a different column offcourse.
The uncheckallbutton is like this:
So far so good (in my opinion) but now the hard part, i cant get it figured out..
Is it possible to create a button or macro which creates a new row with all the checkboxes from the rows above, along with all the 'template' used in all the rows above? The checkboxes should apply to this new row only.
This will help me in two ways:
1) I expect to have 150 rows. When i have the option like described above i dont have to make 150 x 4 checkboxes and macro's by hand.
2) The sheet will become idiot-proof, so that when a new name with new data (and checkboxes) can be added by everyone, simply by using a dropdown menu and adding new dates.
I hope the store makes a little bit sense and mostly i hope someone can help me!
I do this like this:
Code:
A2:A20 = startdate
B2:B20 = Current date generated with =+NOW()
C2:C20 = Signaldate generated with a VBA macro see below at the *1 whichs adds 150 days to the startdate
D2:D20 = End-date generated with a VBA macro see below at the *2, whichs adds 183 days to the startdate
E2:E20 = Give signal, because this has to be between B2 and C2, it will display "CV1" when neccecary =IF(AND(D2>=B2;D2<=C2);"CV1";"")
F2:F20 = Expiration date, and date cannot be higher than the current date, if so: =IF(D2>C2;"Certificate expired!";"")
G2:G20 = Here comes the first VBA-checkbox which adds the amound of days neccecary to go in the next fase.
=== START FASE 2 ===
Like above, so that the days added to the start days increase with 300 and 365 days.
==== This goes on to a fourth and last fase, so a loop of 2 years is completed, after this a VBA-button is pressed which unchecks all checkboxes, so that the loop goes on ====
*1
Code:
Private Sub Chk_A1_Change()
If chk_A1.Value = True Then
Date = Worksheets("Sheet1").Range("B1").Value
Worksheets("Sheet1").Range("B1").Value = Date + 183
Else
Exit Sub
End If
End Sub
*2
Code:
Private Sub ChkA2_Change()
If chk_A1.Value = False Then
Exit Sub
Else
If chk_A2.Value = True Then
Date = Worksheets("Sheet1").Range("B1").Value
Worksheets("Sheet1").Range("B1").Value = Date + 183
Else
Exit Sub
End If
End If
End Sub
Checkbox 3 and 4 are the same as above, but reffer to a different column offcourse.
The uncheckallbutton is like this:
Code:
Private Sub CommandButton1_click()
If CommandButton1.Enabled = True Then
chk_A1.Value = False
CheckBox2.Value = False
CheckBox3.Value = False
CheckBox4.Value = False
Else
If CommandButton1.Enabled = False Then
Exit Sub
End If
End If
End Sub
So far so good (in my opinion) but now the hard part, i cant get it figured out..
Is it possible to create a button or macro which creates a new row with all the checkboxes from the rows above, along with all the 'template' used in all the rows above? The checkboxes should apply to this new row only.
This will help me in two ways:
1) I expect to have 150 rows. When i have the option like described above i dont have to make 150 x 4 checkboxes and macro's by hand.
2) The sheet will become idiot-proof, so that when a new name with new data (and checkboxes) can be added by everyone, simply by using a dropdown menu and adding new dates.
I hope the store makes a little bit sense and mostly i hope someone can help me!