Macro for Calendar
I have a calendar that has names in column a and dates in row 4
Sheets: Calendar, March, April, etc In Sheet "Calendar" Column A Column B Column C Column D 03/01/10 03/02/10 03/03/10 etc. Bob Smith Tom White Fred Brown etc and when i click on an empty cell I already have a userform appear. When the userform appears I want textbox1 to autofill with the contents of the cell in Column A of that active Row and textbox2 to autofill with contents of the cell in the active Column row 4. So if I click on empty cell C6, i want "Tom White" to go in textbox1 and "03/02/10" to go in textbox2 automatically. Textboxes 3-7 will have user input values. Then all the values of the textboxes will be "sent" to another sheet based on the the value in Based on what Sheet Calendar A2 says, ex. March or April or May etc. the values of the textboxes would go to that month sheet and look like this: Column A Column B Column C Column D etc. Tom White 03/02/10 textbox3 value textbox vlaue4 etc. Now, to go one further, is there a way to add another textbox where the user inputs the # of days, lets say 4, and the macro will do this: Column A Column B Column C Column D etc. Tom White 03/02/10 textbox3 value textbox vlaue4 etc. Tom White 03/03/10 textbox3 value textbox vlaue4 etc. Tom White 03/04/10 textbox3 value textbox vlaue4 etc. Tom White 03/05/10 textbox3 value textbox vlaue4 etc. I have a macro that will send the values to a specific sheet already and works fine, but I'm not sure how to change the sheet based on the the value in Calendar A2. That's not necessarily the most important... the example I gave just above is really the most important aspect of my macro. THANKS! |
Macro for Calendar
I'm not sure what's in A2 on the calendar worksheet. However, what I would
probably do is run the date value (03/02/10) through the Left$ function and pull the 2 left characters. monthvalue=(Left$(03/02/10+' ',2))+0. You could then use that value to reference an array of months to get the sheet name. MonthArray(3)="March", etc. You can then use that sheetname to reference the correct worksheet to select. As for the extra textbox with the number of days, that can be a for loop. however, I'm not sure how you're selecting the rows to place the information in the first place. Just be sure to increment that variable within your for loop so that you don't write subsequent data over the first set (unless you want to for some reason). Hope this is clear. Allen. "Excelnewbie" wrote: I have a calendar that has names in column a and dates in row 4 Sheets: Calendar, March, April, etc In Sheet "Calendar" Column A Column B Column C Column D 03/01/10 03/02/10 03/03/10 etc. Bob Smith Tom White Fred Brown etc and when i click on an empty cell I already have a userform appear. When the userform appears I want textbox1 to autofill with the contents of the cell in Column A of that active Row and textbox2 to autofill with contents of the cell in the active Column row 4. So if I click on empty cell C6, i want "Tom White" to go in textbox1 and "03/02/10" to go in textbox2 automatically. Textboxes 3-7 will have user input values. Then all the values of the textboxes will be "sent" to another sheet based on the the value in Based on what Sheet Calendar A2 says, ex. March or April or May etc. the values of the textboxes would go to that month sheet and look like this: Column A Column B Column C Column D etc. Tom White 03/02/10 textbox3 value textbox vlaue4 etc. Now, to go one further, is there a way to add another textbox where the user inputs the # of days, lets say 4, and the macro will do this: Column A Column B Column C Column D etc. Tom White 03/02/10 textbox3 value textbox vlaue4 etc. Tom White 03/03/10 textbox3 value textbox vlaue4 etc. Tom White 03/04/10 textbox3 value textbox vlaue4 etc. Tom White 03/05/10 textbox3 value textbox vlaue4 etc. I have a macro that will send the values to a specific sheet already and works fine, but I'm not sure how to change the sheet based on the the value in Calendar A2. That's not necessarily the most important... the example I gave just above is really the most important aspect of my macro. THANKS! |
All times are GMT +1. The time now is 05:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com