Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on userform date input
I have a workbook that is used for scheduling deliveries. Sheet 1 is a "Control Panel" which among other things will pop up an entry form for "New Deliveries". Sheet 2 is set up as a list which receives the data from the userform. This all works as desired presently, but I want to write the data from the userform to a separate sheet by month based on the delivery date entered on the form. The form has a permanently visible calendar which when clicked writes the selected date to a text box right below the calendar (as mm/dd/yy). How can I pull the month only from the text box and use it to determine which of the monthly sheets will be activated for writing the data from the form? -- mugitty ------------------------------------------------------------------------ mugitty's Profile: http://www.excelforum.com/member.php...o&userid=30713 View this thread: http://www.excelforum.com/showthread...hreadid=503804 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on userform date input
Dim mnth as String
mnth = Format(cdate(Textbox1.Text),"mmm") Produces Mar for example. To get March, change mmm to mmmm if you want a number between 1 and 12 inclusive Dim mnth as Long mnth = Format(cdate(Textbox1.Text),"m") -- Regards, Tom Ogilvy "mugitty" wrote in message ... I have a workbook that is used for scheduling deliveries. Sheet 1 is a "Control Panel" which among other things will pop up an entry form for "New Deliveries". Sheet 2 is set up as a list which receives the data from the userform. This all works as desired presently, but I want to write the data from the userform to a separate sheet by month based on the delivery date entered on the form. The form has a permanently visible calendar which when clicked writes the selected date to a text box right below the calendar (as mm/dd/yy). How can I pull the month only from the text box and use it to determine which of the monthly sheets will be activated for writing the data from the form? -- mugitty ------------------------------------------------------------------------ mugitty's Profile: http://www.excelforum.com/member.php...o&userid=30713 View this thread: http://www.excelforum.com/showthread...hreadid=503804 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on userform date input
Tom; Thank you for your reply. The code you posted looks like it would just change what was presented in the text box? I need the full date to appear in the final spreadsheet that is made active based on the month of the scheduled delivery. Could I use your code to return to another (hidden?) textbox while retaining the full date for writing to the delivery schedule? If so, how would I write the macro that actually selected the appropriate month? ex: If I enter 02/15/06 in the form, sheet "FEB" needs to be made active and then all of the data from the form (including the full date) needs to be written to the first empty row on that sheet. -- mugitty ------------------------------------------------------------------------ mugitty's Profile: http://www.excelforum.com/member.php...o&userid=30713 View this thread: http://www.excelforum.com/showthread...hreadid=503804 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on userform date input
No, my code answered your question.
Dim mnth as String mnth = Format(cdate(Textbox1.Text),"mmm") worksheets(mnth).Select Range("B9").Value = Textbox1.Text Using my code doesn't preclude you from doing whatever else you want with the entry in the textbox. It doesn't affect the textbox value in any way. -- Regards, Tom Ogilvy "mugitty" wrote in message ... Tom; Thank you for your reply. The code you posted looks like it would just change what was presented in the text box? I need the full date to appear in the final spreadsheet that is made active based on the month of the scheduled delivery. Could I use your code to return to another (hidden?) textbox while retaining the full date for writing to the delivery schedule? If so, how would I write the macro that actually selected the appropriate month? ex: If I enter 02/15/06 in the form, sheet "FEB" needs to be made active and then all of the data from the form (including the full date) needs to be written to the first empty row on that sheet. -- mugitty ------------------------------------------------------------------------ mugitty's Profile: http://www.excelforum.com/member.php...o&userid=30713 View this thread: http://www.excelforum.com/showthread...hreadid=503804 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on userform date input
OK - Thanks again for your quick reply! I'll give it a go as soon as the football games are over today. (Have to wait, as I'm from Pittsburgh in the 70's and 80's and now live in SE Washington state - so I'm watching closely to make sure that the Steelers and the Seahawks end up in the Super Bowl) -- mugitty ------------------------------------------------------------------------ mugitty's Profile: http://www.excelforum.com/member.php...o&userid=30713 View this thread: http://www.excelforum.com/showthread...hreadid=503804 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to select a specific range based on the time value of user form input | New Users to Excel | |||
Select cell from range based on input in excel xp | Excel Discussion (Misc queries) | |||
Userform-Change textbox color and font based on input in other tex | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming | |||
select data based on user input | Excel Programming |