Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find free sharware to include calendar pop or use calendar in cell | Excel Discussion (Misc queries) | |||
how do i export excel calendar info to outlook calendar? | Excel Discussion (Misc queries) | |||
Custom Macro View/Print Toolbar for Calendar | Excel Discussion (Misc queries) | |||
Excel to Outlook Calendar Macro | Excel Discussion (Misc queries) | |||
import calendar items from excel into outlook calendar | Excel Discussion (Misc queries) |