PDA

View Full Version : Solved: Calendar Form pass control back to UserForm



gsalois
08-07-2010, 08:31 AM
Hi!
First off - man, am I glad I found this place! I am using an excellent calendar form script provided from here in an Excel 2007 file. What I have is an existing userform with a date field that the user had to type into. Now with the calendar form when the date field is accessed, it brings up the calendarform instead which certainly helps constrain date entries!

What I'm trying to figure out is when the date is selected from the calendar, show it in the date text box on the Userform, pass control to the next entry box on the userform, and then hide the calendar form.

Once the userform is complete and the user clicks okay to enter the record, pass focus back to the date field and pop up the calendar yet again. This should cycle for as many records as the user enters until he/she exits the main userform.

I sure hope I'm explaining this the right way!

Many thanks in advance and again - I'm as new as can be to vb and have to teach myself:dunno !!

Cheers!

Gene

Bob Phillips
08-07-2010, 09:41 AM
Post your workbook for us to see.

gsalois
08-07-2010, 02:52 PM
Ok - I think it's attached!! Thanks!!

slamet Harto
08-08-2010, 02:37 AM
i just add a command button on BoaeqForm
Here is slight revision

in BoaeqForm module
Private Sub CommandButton1_Click()
CalendarForm.Show
End Sub

on Calendarform module
Private Sub CommandButton1_Click() 'OK

'Place the date from the calendar into the active cell
' ActiveWorkbook.Sheets("Sheet 1").Activate
' Range("BoAEqTable").Select
Range("B5").Select
Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Value = CalendarForm.Calendar1.Value
BoAEqForm.DateTextBox.Value = CalendarForm.Calendar1.Value
' Here I want to then pass the control from the Calendar selection (which should populate the date cell in the BoAEqForm
' and the move to the next text box
'in the BoAEqForm, continue filling the data boxes
' and then when I hit enter on the BoAEqForm, pass control back to the date field and bring the calendar back up.
' When the calendar comes back up, I want to start the process all over again!

'Dismiss the userform
' Unload Me

End Sub

hope this help

gsalois
08-08-2010, 05:22 AM
Thanks! I was instead hoping that the calendar wouldd show once the date field has focus then after the date is selected on the calendar, it writes the date into the date text box and moves automatically to the next text box. Is that possible?

Thanks!!!

Bob Phillips
08-08-2010, 05:30 AM
Try

gsalois
08-08-2010, 05:39 AM
That did it! Wow - excellent and thank you very much!!

gsalois
08-08-2010, 05:42 AM
Hopefullly, last question on this - (I warned you I'm as new as can be!)

How do I mark this as "answered"? Sorry - I'm looking but I don't see it. Sure it's there and I probably just need more coffee!!

Thanks again - this place is great!:cloud9:
I think the thumbs up icon?

gsalois
08-08-2010, 05:58 AM
Not to be a total bozo but there's just one small point. When I run the code it does just what's needed but it also writes the date value into whatever cell the sheet happens to be on. It does write the record correctly at the end but the date is also just added to the sheet by itself wherever the cell happened to be. Anyway to get rid of that?

Sorry for the premature "it works" - well, it does but...

And thanks so much! I'm trying to prove that even at my age - I can learn somehting new! Cheers!

Bob Phillips
08-08-2010, 06:44 AM
Hopefullly, last question on this - (I warned you I'm as new as can be!)

How do I mark this as "answered"? Sorry - I'm looking but I don't see it. Sure it's there and I probably just need more coffee!!

Thanks again - this place is great!:cloud9:
I think the thumbs up icon?

It is under the Thread Tools dropdown

gsalois
08-09-2010, 12:36 PM
:think: Don't see the thread tools dropdown...

This is going to be a d'oh moment for sure... My apologies in advance!

Bob Phillips
08-09-2010, 12:52 PM
Just above the first post in the thread.