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

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

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

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

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