PDA

View Full Version : User Form



vwsteven
04-13-2011, 12:25 AM
Hi all

I'm writing a code for a user form in VBA excel.

I have two columns of text boxes, and what I would like is to enter dates in the left column, and then that date minus two dates, should appear automatically in the right column but I want to be able to amend that manually.

I tried

If IsDate(TextBox8.Text) Then

TextBox8.Text = CDate(TextBox28.Text) - 2

End If

but that doesn't seem to work. any ideas?

thanks

mbm123
04-13-2011, 05:22 AM
What does the "-2" mean? minus 2 days?

you can define something as "date"

dim mydate as date

and then add or remove days by function

mydate - days(2) or some other sort

Jan Karel Pieterse
04-13-2011, 05:25 AM
What about (note I changed the textbox numbers too!):

If IsDate(TextBox8.Text) Then

TextBox28.Text = Format(CDate(TextBox8.Text) - 2,"dd/mm/yyyy")

End If

vwsteven
04-13-2011, 11:46 PM
Hi
i tried to do it as you said

I put the function in a new Sub right? Or do I have to write it in the code of the userform itself??

so I have:


Private Sub TextBox8()

If IsDate(TextBox8.Text) Then

TextBox28.Text = Format(CDate(TextBox8.Text) - 2, "dd/mm/yyyy")

End If

End Sub


Unfortunately this still doesn't work for me. When I enter the date, nothing appears in the other text box. Do I have to call the function or something?
And I don't have to put anything in for the dd/mm/yyyy right? because this will always depend on what's written in the first column.
thanks!

Jan Karel Pieterse
04-14-2011, 01:34 AM
You have not created the proper change event routine for the textbox.
Doubleclick on the textbox in the userform in the VBA editor. The editor should open a new event stub for the default Change event of the textbox. Put the code there. Remove the routine you posted as it will not do anything.

Once in the code window behind a form you can use the two dropdowns at the top of the window. The left one has all objects in the form and after selecting one of them, the one on the right shows the available events that object has to offer. Selecting an object usually makes the VBA editor insert the default event for that control automatically, remove it if you don't need it.

vwsteven
04-14-2011, 08:48 AM
Hey

thanks for your answer.
I entered it in the code for of the textbox in which the date should appear. it still doesnt work though, when I enter a date in the first column.

the dropdown menu I left on "Change". is that correct??

thank you!

Jan Karel Pieterse
04-14-2011, 12:00 PM
Your code should look like this:


Private Sub TextBox8_Change()
If IsDate(TextBox8.Text) Then
TextBox28.Text = Format(CDate(TextBox8.Text) - 2, "dd/mm/yyyy")
End If
End Sub

vwsteven
04-15-2011, 02:28 AM
Hi!

thanks a lot! it works now!!

Regards