Automatically select correct range
Within one worksheet, I have a "form" (for lack of a better word) that
represents a year. I copy the previous year and paste below it for the current year data to be input. The same macro is used to enter that data into each year. I do not want to enter directly in the spreadsheet, but rather via a userform. SPREADSHEET SETUP: The spread sheet is desiged to indicate the date I get paid. Since I get paid, say on Jan 4, then the days that I worked are in the previous year. With that in mind, this is what Column A (Week #) & Column B (date) looks like. Each date is the date for each Monday of the year. A B 50 12/17/07 51 12/24/07 52 12/31/07 1 1/7/08 2 1/14/08 3 1/21/08 continues to 49 12/8/08 Here is my question/problem. There will be multiple forms within a worksheet, one for each year. I need the macro to somehow know which year (form) is current so that when a user enters the date worked in textbox1, there will be a check to verify that the date is valid for current year. Remember the current year of work will include a couple of weeks or so from the end of the previous year. Thanks, Les |
Automatically select correct range
The simpliest way is to add a new column (or cell at top of form) that
contains the sheet name or the fisical year which is part of the sheet name. You could also add at the top of the form a year start date and year end date and make sure the entered date fall between these tow dates. "WLMPilot" wrote: Within one worksheet, I have a "form" (for lack of a better word) that represents a year. I copy the previous year and paste below it for the current year data to be input. The same macro is used to enter that data into each year. I do not want to enter directly in the spreadsheet, but rather via a userform. SPREADSHEET SETUP: The spread sheet is desiged to indicate the date I get paid. Since I get paid, say on Jan 4, then the days that I worked are in the previous year. With that in mind, this is what Column A (Week #) & Column B (date) looks like. Each date is the date for each Monday of the year. A B 50 12/17/07 51 12/24/07 52 12/31/07 1 1/7/08 2 1/14/08 3 1/21/08 continues to 49 12/8/08 Here is my question/problem. There will be multiple forms within a worksheet, one for each year. I need the macro to somehow know which year (form) is current so that when a user enters the date worked in textbox1, there will be a check to verify that the date is valid for current year. Remember the current year of work will include a couple of weeks or so from the end of the previous year. Thanks, Les |
Automatically select correct range
Reading your reply got me thinking. If I NAME the range, ie 2007, 2008, etc,
could I make the check based on YEAR(Date)? If this can be done, Then I need to set the range or determine an OFFSET value based on the NAME value. How is a NAME referenced within a macro? Thanks, Les "Joel" wrote: The simpliest way is to add a new column (or cell at top of form) that contains the sheet name or the fisical year which is part of the sheet name. You could also add at the top of the form a year start date and year end date and make sure the entered date fall between these tow dates. "WLMPilot" wrote: Within one worksheet, I have a "form" (for lack of a better word) that represents a year. I copy the previous year and paste below it for the current year data to be input. The same macro is used to enter that data into each year. I do not want to enter directly in the spreadsheet, but rather via a userform. SPREADSHEET SETUP: The spread sheet is desiged to indicate the date I get paid. Since I get paid, say on Jan 4, then the days that I worked are in the previous year. With that in mind, this is what Column A (Week #) & Column B (date) looks like. Each date is the date for each Monday of the year. A B 50 12/17/07 51 12/24/07 52 12/31/07 1 1/7/08 2 1/14/08 3 1/21/08 continues to 49 12/8/08 Here is my question/problem. There will be multiple forms within a worksheet, one for each year. I need the macro to somehow know which year (form) is current so that when a user enters the date worked in textbox1, there will be a check to verify that the date is valid for current year. Remember the current year of work will include a couple of weeks or so from the end of the previous year. Thanks, Les |
Automatically select correct range
Excerl won't except 2007 or 2008 as a name range. but you could have _2007
or _2008. Then Range("_" & Year(Date)). But the problem is how do you know when the form spans two years which is the correct year? The formula for figuring out if a week is within the last two weeks of a year is complicated because Monday can be any of 7 dffierent dates. To keep things simple I recomment on the form to have the Start Date and End Date. "WLMPilot" wrote: Reading your reply got me thinking. If I NAME the range, ie 2007, 2008, etc, could I make the check based on YEAR(Date)? If this can be done, Then I need to set the range or determine an OFFSET value based on the NAME value. How is a NAME referenced within a macro? Thanks, Les "Joel" wrote: The simpliest way is to add a new column (or cell at top of form) that contains the sheet name or the fisical year which is part of the sheet name. You could also add at the top of the form a year start date and year end date and make sure the entered date fall between these tow dates. "WLMPilot" wrote: Within one worksheet, I have a "form" (for lack of a better word) that represents a year. I copy the previous year and paste below it for the current year data to be input. The same macro is used to enter that data into each year. I do not want to enter directly in the spreadsheet, but rather via a userform. SPREADSHEET SETUP: The spread sheet is desiged to indicate the date I get paid. Since I get paid, say on Jan 4, then the days that I worked are in the previous year. With that in mind, this is what Column A (Week #) & Column B (date) looks like. Each date is the date for each Monday of the year. A B 50 12/17/07 51 12/24/07 52 12/31/07 1 1/7/08 2 1/14/08 3 1/21/08 continues to 49 12/8/08 Here is my question/problem. There will be multiple forms within a worksheet, one for each year. I need the macro to somehow know which year (form) is current so that when a user enters the date worked in textbox1, there will be a check to verify that the date is valid for current year. Remember the current year of work will include a couple of weeks or so from the end of the previous year. Thanks, Les |
Automatically select correct range
I manually create (copy/paste previous) the form. The first date is set and
the remainder is calculated via adding 7 to previous cell (date). I already have the formula that can calculate the placement of hours worked using the date worked (entered on userform) and the dates in the spreadsheet (column B). I just need to be able to tell which calender year is current and check it against the date entered to determine if valid. Once I determine which form is current, then I can determine OFFSET. Thanks, Les "Joel" wrote: Excerl won't except 2007 or 2008 as a name range. but you could have _2007 or _2008. Then Range("_" & Year(Date)). But the problem is how do you know when the form spans two years which is the correct year? The formula for figuring out if a week is within the last two weeks of a year is complicated because Monday can be any of 7 dffierent dates. To keep things simple I recomment on the form to have the Start Date and End Date. "WLMPilot" wrote: Reading your reply got me thinking. If I NAME the range, ie 2007, 2008, etc, could I make the check based on YEAR(Date)? If this can be done, Then I need to set the range or determine an OFFSET value based on the NAME value. How is a NAME referenced within a macro? Thanks, Les "Joel" wrote: The simpliest way is to add a new column (or cell at top of form) that contains the sheet name or the fisical year which is part of the sheet name. You could also add at the top of the form a year start date and year end date and make sure the entered date fall between these tow dates. "WLMPilot" wrote: Within one worksheet, I have a "form" (for lack of a better word) that represents a year. I copy the previous year and paste below it for the current year data to be input. The same macro is used to enter that data into each year. I do not want to enter directly in the spreadsheet, but rather via a userform. SPREADSHEET SETUP: The spread sheet is desiged to indicate the date I get paid. Since I get paid, say on Jan 4, then the days that I worked are in the previous year. With that in mind, this is what Column A (Week #) & Column B (date) looks like. Each date is the date for each Monday of the year. A B 50 12/17/07 51 12/24/07 52 12/31/07 1 1/7/08 2 1/14/08 3 1/21/08 continues to 49 12/8/08 Here is my question/problem. There will be multiple forms within a worksheet, one for each year. I need the macro to somehow know which year (form) is current so that when a user enters the date worked in textbox1, there will be a check to verify that the date is valid for current year. Remember the current year of work will include a couple of weeks or so from the end of the previous year. Thanks, Les |
All times are GMT +1. The time now is 12:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com