PDA

View Full Version : Solved: Userform Calendar Control modification



U_Shrestha
03-31-2008, 07:24 AM
Hello, I was going through Kbase and came across lucas' Userform Calendar control and liked it very much.

Can I ask this forum for following modification:

In the attached sheet, I have embedded two text boxes, one in B5 and other in B6. I want the calendar userform to be initialized when the text box in B5 is clicked and its value should go in the cell B5. I don't know if the text value would be considered a cell value or no. I want the same action to trigger when the text box in B6 is clicked. The date format I am looking for is m/d/yyyy. Can someone help me please? Thanks.

lucas
03-31-2008, 08:40 AM
I'm confused as to why you would be using a textbox on the sheet......
the userform can be called using gotfocus....example below that calls a messagbox......goes in the module for the sheet of course.

coding for adding the date to the textbox would be quite a bit more work.....are you sure this is the method path you must follow?



Private Sub TextBox1_GotFocus()
MsgBox "test"
End Sub

U_Shrestha
03-31-2008, 09:02 AM
Hello Lucas,

Thanks for the response. I am very curious to know if the selected date from the calendar form goes inside the text boxes with are embedded in B5 and B6. The data from B5 and B6 goes to a table. I do not know if putting the text inside the text box1 which is embedded in B5 would automatically go to B5.

I want to learn this if it does not increase too much of the file size. Thanks.

lucas
03-31-2008, 09:15 AM
The data from B5 and B6 goes to a table.

Why can't the data in cell B5 and B6 go to your table?

I don't understand why you feel compelled to use a textbox on a sheet.......it seems wrong to me to use textbox controls on the sheet but maybe you have a good reason.......just wondering what it is.

U_Shrestha
03-31-2008, 09:27 AM
I have a table that pulls up a data based on the Start Date (B5) and End Date (B6) in the same sheet. It pulls up intersheet data based on the selected time period and populates the table, this data goes to a chart. So in the spreadsheet I want to show only the text box with start data and end date texts boxes along with its labels and the chart only. I think this feature will be more userfriendly for some of my workbook users. I am even hiding the actual populated table so that only the Chart and the two text boxes would be visible.

lucas
03-31-2008, 11:16 AM
-u,
I'm sorry I didn't get back to this sooner......we just had a huge thunderstorm go through and I lost power for a while....

try the attached and see if it is what you need.

U_Shrestha
03-31-2008, 11:40 AM
Hello Lucas,

Sorry to hear about the thunderstorm. It is still raining here in NY.

Thank you for the modified attachment. The date does go inside the text boxes perfectly, now, another final step is, how do I get this date from text box to other cells in the spreadsheet. What I mean is, I selected B30 and entered formula =B5, but I didn't get the date that was inside the text box embedded in B5. How can I retrieve this date in other cell? Thanks.

lucas
03-31-2008, 11:48 AM
that's why I asked you why you were using textboxes......

of course you didnt get the value of the textbox because that value would be:
textbox1.value
which would be very difficult to call from a cell formula.

lucas
03-31-2008, 11:59 AM
On the code for the sheet you can add this line to put the value in cell B30 every time the textbox changes.

Private Sub TextBox1_GotFocus()
If CalendarFrm.HelpLabel.Caption <> "" Then
CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
Else: CalendarFrm.Height = 191
CalendarFrm.Show
End If
Range("B30") = TextBox1.Value
End Sub

see attached.....I fixed textbox1 only because I didnt know where you wanted the value for textbox2 to go.

U_Shrestha
03-31-2008, 12:05 PM
Awsome!!! That's exactly what I was looking for. Thank you very much.