MrExcel Publishing
Your One Stop for Excel Tips & Solutions

EIF TBOX1 = 2,4,6,9,11 THEN TBOX2 =


Posted by Susan Smith on November 15, 2001 7:18 AM

Hi Guys,

I want to say :-

If tb1 is 2 then tbox2 =28
If it is = 4,6,9,11 then tbox2 = 30
And for any other number tbox2 =31

Can you help me

Sue


Posted by Bob Umlas on November 15, 2001 7:28 AM


Not sure what tbox2 is, but assuming it's a range name, then you should first BE in tbox2, enter:
=IF(tb1=2,28,if(tb1=or({4,6,9,11}),30,31))

Posted by Rick E on November 15, 2001 7:41 AM

30 Days has September,,,,

I assume these are text boxs on a form so here is the code on the form:

Private Sub TextBox1_Change()
numberIn = TextBox1.Text
Select Case numberIn
Case 2
TextBox2.Text = 28
Case 4, 6, 9, 11
TextBox2.Text = 30
Case Else
TextBox2.Text = 31
End Select
End Sub


Good Luck

Posted by Sue on November 15, 2001 9:30 AM

Re: 30 Days has September,,,,I wrote code for it and it worked, your's much shorter!!! Thanks


Posted by Rick E on November 15, 2001 10:44 AM

Re: Added code...

Here is the code with checks that the number entered is between 1 and 12:

Private Sub TextBox1_Change()
numberIn = TextBox1.Text
If numberIn = "" Then
TextBox2.Text = ""
Exit Sub
End If
If IsNumeric(numberIn) Then
If numberIn > 0 And numberIn < 13 Then
Select Case numberIn
Case 2
TextBox2.Text = 28
Case 4, 6, 9, 11
TextBox2.Text = 30
Case Else
TextBox2.Text = 31
End Select
Else
TextBox1.Text = ""
str1 = "Must enter a value between 1 and 12"
i = MsgBox(str1, vbExclamation + vbOKOnly, "Please enter a number")
End If
Else
TextBox1.Text = ""
str1 = "Must enter a value between 1 and 12"
i = MsgBox(str1, vbExclamation + vbOKOnly, "Please enter a number")
End If
End Sub

This is a lot better, would you not agree? : I assume these are text boxs on a form so here is the code on the form


Posted by Ian Mac on November 16, 2001 2:56 AM

Does not February have 29 every 4 years AND every century? (NT)