Why this code works and other do not? Newbie here.

[Steve2005black]

Hi

I am a newbie to VBA.

I have a UserForm with a Calendar and a TextBox. I wrote a procedure to
check the number entered in the TextBox do not exceed a limit. The
following function triggers the MsgBox and resets the TextBox1.Value to
0, even when the TextBox1.Value DO NOT exceed the limit set.

----
Private Sub TextBox1_AfterUpdate()

If TextBox1.Value > ((ActiveSheet.Range("$C$5").Value + 1)) -
Calendar1.Day Then
MsgBox "Leave cannot exceed " & ((ActiveSheet.Range("$C$5").Value +
1)) - Calendar1.Day & " days!", vbCritical, "Wrong number of leave"

TextBox1.Value = 0

End If

End Sub
----

Finally after a couple of hours I came with this solution and it works
the way I need. However I am curious why the first code didn't work.
Can someone please guide me?


----
Private Sub TextBox1_AfterUpdate()
Dim DateLimit As Integer
DateLimit = TextBox1.Value
If DateLimit > ((ActiveSheet.Range("$C$5").Value + 1)) -
Calendar1.Day Then
MsgBox "Leave cannot exceed " & ((ActiveSheet.Range("$C$5").Value +
1)) - Calendar1.Day & " days!", vbCritical, "Wrong number of leave"

TextBox1.Value = 0

End If

End Sub

 

[Norman Jones]

Hi Steve,

Your first version works for me with an explicit conversion of TextBox1's
string value to an integer:

Private Sub TextBox1_AfterUpdate()

If CInt(TextBox1.Value) > (ActiveSheet.Range("$C$5").Value + 1) _
- Calendar1.Day Then
MsgBox "Leave cannot exceed " & _
(ActiveSheet.Range("$C$5").Value + 1) _
- Calendar1.Day & " days!", vbCritical, _
"Wrong number of leave"

TextBox1.Value = 0

End If
End Sub

Your second version makes an implicit conversion by assigning the TextBox
value to the DateValue variable which you have dimmed as integer.

 

[Ian]

Are you sure your calculation is correct? Is C5 definitely a numerical
value?

Try adding Range("A1").value= (ActiveSheet.Range("$C$5").Value + 1) -
Calendar1.Day before the IF statement to double check that the calculated
value is what you expect.