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

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

-----

TIA

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

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.

---
Regards,
Norman



wrote in message
oups.com...
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

-----

TIA

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

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.

--
Ian
--
wrote in message
oups.com...
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

-----

TIA