W
WLMPilot
I have a worksheet that contains information about pay, holidays working/not
working, etc., for each year, starting with 2006. Each year is four columns
wide.
Regarding the holiday section of each year, I list the holidays as follows
and have a checkbox that will be TRUE (checked) if working that holiday.
<---------------------2009------------------------->
P Q R S <---columns
Holiday | hrs pd | ckbox | date of holiday |
Holiday | hrs pd | ckbox | date of holiday |
Holiday | hrs pd | ckbox | date of holiday |
Based on the value of each checkbox, a macro will place a value of 8 hrs or
16 hrs in the "hrs pd" (column Q). Below is a macro used for one of the
holidays for 2008:
Private Sub CheckBox23_Click()
If CheckBox23.Value = True Then
Worksheets("Pay-Calc").Cells(10, 12).Value = 16
Else: Worksheets("Pay-Calc").Cells(10, 12).Value = 8
End If
End Sub
As each year approaches, I copy/paste and setup that year, adding 11 more
checkboxes and their individual macro.
Referencing above macro, the holidays are listed on rows 10-20 inclusive.
The column value of the .Cells(10, 12) increases by 5 each year, ie 2006 was
2, 2007 was 7, 2008 was 12, and 2009 is 17.
I am wondering if there is a way to combine everything into one macro per
year that will look at the checkboxes for that year instead of having 11
individual macros as shown above?
Thanks for your help!
Les
working, etc., for each year, starting with 2006. Each year is four columns
wide.
Regarding the holiday section of each year, I list the holidays as follows
and have a checkbox that will be TRUE (checked) if working that holiday.
<---------------------2009------------------------->
P Q R S <---columns
Holiday | hrs pd | ckbox | date of holiday |
Holiday | hrs pd | ckbox | date of holiday |
Holiday | hrs pd | ckbox | date of holiday |
Based on the value of each checkbox, a macro will place a value of 8 hrs or
16 hrs in the "hrs pd" (column Q). Below is a macro used for one of the
holidays for 2008:
Private Sub CheckBox23_Click()
If CheckBox23.Value = True Then
Worksheets("Pay-Calc").Cells(10, 12).Value = 16
Else: Worksheets("Pay-Calc").Cells(10, 12).Value = 8
End If
End Sub
As each year approaches, I copy/paste and setup that year, adding 11 more
checkboxes and their individual macro.
Referencing above macro, the holidays are listed on rows 10-20 inclusive.
The column value of the .Cells(10, 12) increases by 5 each year, ie 2006 was
2, 2007 was 7, 2008 was 12, and 2009 is 17.
I am wondering if there is a way to combine everything into one macro per
year that will look at the checkboxes for that year instead of having 11
individual macros as shown above?
Thanks for your help!
Les