MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Damon Ostrander or Anyone - Using to textboxes and formulas

Posted by Beverley B on October 31, 2001 7:58 AM

Hi there,

let me explain first. If our 2002 financial year starts in october 2001, the period i would use for october would be 0201 - 02 would be the year and 01 the month. what i want to do is to use the month and year which are in 2 textboxes to form a period in a third textbox can this be done.


month textbox1 = 12 (december)this is 3rd month in finanacial year

year textbox2 = 01 (2001)

I want to write code including a formula to calculate the period which would be 0203.

Can anyone help me with this???

Posted by Joe Chin on October 31, 2001 10:20 AM

You could use something like this:

Private Function ConvertDate(intMonth as integer, intYear as integer) as String
Dim strNewDate as String * 4
Dim intNewMonth as Integer
Dim strNewMonth as String * 2
Dim intNewYear as Integer
Dim strNewYear as String * 2
Dim intNewDate as Integer

If intMonth < 10 then
intNewMonth = intMonth + 3
intNewYear = intYear
intNewMonth = intMonth - 9
intNewYear = intYear + 1
End If

strNewMonth = cstr(intNewMonth)
strNewYear = cstr(intNewYear)
strNewDate = strNewYear & strNewDate

ConvertDate = strNewDate

End Sub

Posted by BeverleyB on November 01, 2001 9:04 AM

Joe Chin - Using to textboxes and formulas

Thanks for the code but as I have had no training on this some of the code makes absolutley no sense to me sometimes but i try. I am not sure exactly how this would work and it seems to me that i should have code for the textbox which would register the end result. Am I right or am I up a creek without a paddle.


Posted by Joe Chin on November 01, 2001 9:56 AM

Re: Joe Chin - Using to textboxes and formulas

You're up a creek, but I am the paddle . . .
The code I gave you is a function that accepts 2 arguments (intMonth and intYear) and returns a string. Once again, I haven't tested this, but basically, this is how you would call it if you have 3 text boxes: txtMonth, txtYear, and txtYearMonth (which provides the result). I'll create a command button called cmdButton so you have an event to tie it to.

Private Sub cmdButton_Click()
txtYearMonth.Text = ConvertDate(txtMonth.Text,txtYear.Text)
End Sub

This sub plugs the month and year (from the textboxes) into the ConvertDate function. The ConvertDate function does its magic, then answers with the converted date as a string. The string then gets passed into the text for txtYearMonth.

Posted by BeverleyB on November 02, 2001 4:09 AM

Re: Joe Chin - Using to textboxes and formulas

My problem now, is that this is a form for an overseas user and my boss wants it has only the controls that apply to them. Therefore the command button is not going to work in this instance. Can't the convert function be called after the data is punched into txtYear and the textbox loses focus. This is just an idea. Am I making any sense??